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