Sample Header Ad - 728x90

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

2 votes
1 answer
744 views
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](https://dba.stackexchange.com/a/263986/259283) , [second](https://stackoverflow.com/a/56753558/14481500) . 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.
Asked by Biller Builder (288 rep)
Sep 27, 2022, 05:00 PM
Last activity: Sep 30, 2022, 03:58 AM