2

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.

1 Answer 1

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

7
  • 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. Sep 28, 2022 at 11:55
  • 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 Sep 29, 2022 at 9:49
  • 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.
    – dwhitemv
    Sep 29, 2022 at 14:21
  • 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. Sep 29, 2022 at 15:58
  • I added some additional information to the answer.
    – dwhitemv
    Sep 30, 2022 at 4:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.