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
Last activity: Sep 30, 2022, 03:58 AM