How do you run "SELECT" on the composite type argument of an SQL function?

Question

Example: https://dbfiddle.uk/bCSwVpd9

Database:

CREATE TABLE entities (
 id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
CREATE TYPE entity_id AS (
 id bigint
);

Function:

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$;

The crutch is I want to write a paginated multi-select function which could work both from pagination info and a set of ids. The problem with the function above it crashes with:

ERROR: relation "entity_ids" does not exist
LINE 22: entity_ids

There are similar responses to this problem: first, second. However they revolve around argument being an identifier string, not a composite record type and also use plpgsql, which might or might nor be important.

Answer 1

The question title and question body ask different questions, so I will address both.

As respects the question title: You don't need to SELECT against a composite type, you can reference fields in it directly using dot notation. For example, if your type is defined as

CREATE TYPE entity_id AS (
 id bigint,
 name text
);

and an instance of the type is passed to a function with an argument named foo, you can reference the fields as foo.id and foo.text, respectively.

As respects the question body: You can't pass a recordset as a function argument, but you can convert it to an array and pass that. In your case, use an array as the id list argument, and the = ANY operator to search it:

CREATE OR REPLACE FUNCTION get_entities(pagination_limit bigint DEFAULT 25, pagination_offset bigint DEFAULT 0, entity_ids bigint[] DEFAULT NULL)
 RETURNS TABLE(id bigint)
 LANGUAGE sql
AS $function$
 WITH input_entities AS (
 SELECT
 id
 FROM
 entities
 WHERE
 entity_ids IS NULL OR id = ANY ( entity_ids )
 ORDER BY
 id ASC
 LIMIT pagination_limit
 OFFSET pagination_offset
 )
 SELECT
 id
 FROM
 input_entities
 ORDER BY
 id
$function$

Call like:

SELECT * FROM get_entities(25,0,ARRAY[1,2,3,4,5]::bigint[]);

Or to use the output of a subquery, use the ARRAY() constructor:

SELECT * FROM get_entities(25,0,ARRAY(SELECT ids FROM mytable ...));

Since you wanted to see how to pass a recordset from a table-valued function to another:

SELECT * FROM get_entities(25,0,ARRAY(SELECT id FROM myfunc());

Of course, the constructor is unnecessary if myfunc() returns an array.

The two ideas expressed above can be combined; you can have an array of composite type instances.

Reference: https://www.postgresql.org/docs/14/arrays.html

• Biller Builder: I specifically wanted to avoid making it an array because then every function like this has to transform it into a record and the consumer has to transform the record into an array for each function like this. I don't follow on your generated identity column comment. As far as types concerned id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY is just column of type bigint, it being an identity is a domain/constraint detail. So my idea was that consumers can just SELECT id FROM tablename/temp_tablename and pass it as an argument to the function.
• Biller Builder: Okay, turns out it's not possible to have a table/recordset as an argument to a function. So add a create_entities() function your answer which invokes get_entities() on the final set (to show how it is supposed to work within queries) and I'll accept your answer. For the reference see dbfiddle.uk/nBffoDMj
• user234725: Sorry, I don’t understand what you want me to do... you want me to copy your create_entities() function from your fiddle into my answer? Your fiddle looks correct.
• Biller Builder: Either copy or write your own which would add entities and then pass the IDs of newly created entities as an array to the get_entities() instead of just RETURNING. This will show how to pass the array argument from the set of records and that functions are composable.
• user234725: I added some additional information to the answer.