How to create the value of `SETOF <sometype>` type by hand in PostgreSQL?

Question

PostgreSQL has SETOF <sometype> type according to the doc and SETOF <sometype> type is used in a function as shown below:

                               -- ↓ ↓ ↓ Here ↓ ↓ ↓                         
CREATE FUNCTION my_func() RETURNS SETOF <sometype>
...

Now, I want to create the value of SETOF <sometype> type by hand like we can create an array and create a row with ROW() by hand as shown below:

SELECT ARRAY[1,2,3]::INT[]; -- {1,2,3}

SELECT ROW(1,'John',27); -- (1,John,27)

So, how can I create the value of SETOF <sometype> type by hand?

Laurenz Albe · Accepted Answer · 2024-02-06 07:43:38Z

2

One solution is to use a VALUES statement:

VALUES ('someval'::sometype),
       ('otherval'::sometype),
       ...;

You can use such a statement as a subquery. Another way is to unnest an array:

unnest('{someval,otherval}'::sometype[])

answered Feb 6, 2024 at 7:43

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Super Kai - Kazuya Ito · Accepted Answer · 2024-02-10 18:01:51Z

For example, you can create the value of SETOF INT type as shown below:

VALUES (1), (2), (3);

SELECT unnest(ARRAY[1,2,3]);

SELECT generate_series(1,3);

SELECT generate_subscripts(ARRAY['apple','orange','banana'], 1);

And, you can create the value of SETOF TEXT type as shown below:

VALUES ('John'), ('David'), ('Robert');

SELECT unnest(ARRAY['John','David','Robert']);

And, you can create the value of SETOF RECORD type as shown below:

VALUES ('John','Smith'), ('David','Miller');

SELECT unnest(ARRAY[ROW('John','Smith'),ROW('David','Miller')]);

And, you can create the value of SETOF INT[] type as shown below:

VALUES (ARRAY[1,2,3]), (ARRAY[4,5,6]);

And, you can create the value of SETOF TEXT[] type as shown below:

VALUES (ARRAY['John','David']), (ARRAY['Robert','Mark']);

*Memos:

The doc explains unnest().
The doc explains generate_series() and generate_subscripts().
You cannot create the value of SETOF RECORD[] type because SETOF RECORD[] type doesn't exist in PostgreSQL so if you use SETOF RECORD[] type, there is error. *RECORD[] itself doesn't exist in PostgreSQL so if you use RECORD[], there is the error.
My answer explains how to create the array of rows.
My answer explains how to create and use the 1D array with INT[] in detail.
My answer explains how to create and use the 1D(one-dimensional) array with VARCHAR[] in detail.