By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE entities (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
CREATE TYPE entity_id AS (
id bigint
);
CREATE TABLE
CREATE TYPE
CREATE FUNCTION get_entities (
pagination_limit bigint DEFAULT 25,
pagination_offset bigint DEFAULT 0,
entity_ids entity_id DEFAULT NULL
)
RETURNS TABLE (
id bigint
)
LANGUAGE SQL
AS $BODY$
WITH input_entities AS (
SELECT
id
FROM
entities
WHERE
-- filter by id list if provided
entity_ids IS NULL OR id IN (
SELECT
id
FROM
entity_ids
)
ORDER BY
id ASC
LIMIT pagination_limit
OFFSET pagination_offset
)
SELECT
id
FROM
input_entities
ORDER BY
id
$BODY$;
ERROR: relation "entity_ids" does not exist LINE 22: entity_ids ^