Question 336331 in PostgreSQL Question posted in
The official documentation can be found here.

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

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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')]);

  2. 0

    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[])
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search