0

I have the following function. It is something I am building and want to show the contents of stuff_list so I can prove it is working correctly. I can't however work out what output type I should be using:

CREATE OR REPLACE FUNCTION isf.update_tbl_variadic(VARIADIC _stuff_array stuff[])
  RETURNS SETOF BIGINT[]
  LANGUAGE plpgsql AS
$$
DECLARE
    stuff_list BIGINT[];
BEGIN
    
    stuff_list:= ARRAY (
        SELECT id
    FROM stuff As s
    WHERE s."id"
    IN (
    SELECT t.id
    FROM unnest(_stuff_array) as t(
       id,
       x,
       y,
       created
   )))
 FOR UPDATE;

    RAISE NOTICE '%', stuff_list;
    RETURN stuff_list;
END;
$$;

Is anyone able to help me with this please? I need to generate an array from this to be used in a subsequent query, but for now I just need to see the output.

1
  • Did you run SET client_min_messages = notice; before calling your function?
    – user1822
    Commented Oct 3, 2021 at 16:13

1 Answer 1

0

Since the function is declared as

RETURNS SETOF bigint[]

You need RETURN NEXT:

RETURN NEXT stuff_list;

Alternatively, to return a single array:

RETURNS bigint[]
...
RETURN stuff_list;

Or to return a set of bigint:

RETURNS SETOF bigint
...
RETURN QUERY SELECT unnest(stuff_list);

(Or return IDs directly, instead of aggregating and unnesting back.)

1
  • Thank you Erwin :)
    – user973347
    Commented Oct 4, 2021 at 8:16

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.