PostgreSQL: how to get & set an enumerated list variable in a script?

Question

I am using GUC style variables in an SQL script like this:

set mycustom.var = 5;
select current_setting('mycustom.var');

that works fine for strings and integers... but how do I get and set enumerated lists of integers?

Ideally, I'd like to populated the enumerated list with random unique values using this code:

SELECT   num
FROM     GENERATE_SERIES (1, 10) AS s(num)
ORDER BY RANDOM()
LIMIT    6

Erwin Brandstetter · Answer 1 · 2019-01-02T19:00:44.367

1

The problem to overcome: SET expects literal input. You can't feed the result of a query to it directly.

One way around it: dynamic SQL like:

DO
$$
BEGIN
   EXECUTE format(
      'SET mycustom.var = %L'
     , ARRAY(
         SELECT *
         FROM   generate_series(1, 10) 
         ORDER  BY random()
         LIMIT  6
         )::text
      );
END
$$;

Or use set_config():

SELECT set_config('mycustom.var'
                , ARRAY(
                     SELECT *
                     FROM   generate_series(1, 10) 
                     ORDER  BY random()
                     LIMIT  6
                     )::text
                , false);

Then:

SELECT current_setting('mycustom.var')::int[];

db<>fiddle here

This returns an array of integer: int[].

A temporary function would be an alternative. Possibly with a built-in dynamic result (while this solution only stores the result, immutably):

Is there a way to define a named constant in a PostgreSQL query?

edited Jan 02 '19 at 19:00

answered Jan 02 '19 at 18:44

Erwin Brandstetter

605,456
145
1,078
1,228

hmm, I get 2 errors: [Code: , SQL State: 42601] ERROR: syntax error at or near "BEGIN" Position: 4 [Code: , SQL State: 42601] ERROR: syntax error at or near "SELECT" Position: 7 – user952342 Jan 02 '19 at 18:47
Works for me, I tested with Postgres 11. See the added fiddle. – Erwin Brandstetter Jan 02 '19 at 18:49
This returns an array of integer: `int[]`. Do you need an actual *list*? If so, in what form and data type exactly? In case you are running an outdated version of Postgres, you need to declare that. – Erwin Brandstetter Jan 02 '19 at 18:51

score 0 · Accepted Answer · answered Jan 02 '19 at 18:55

Use set_config()

select set_config(
    'mycustom.list', 
    (
        select array_agg(num)::text
        from (
            select num
            from generate_series (1, 10) as s(num)
            order by random()
            limit 6
        ) s
    ),
    false
);

Of course, the setting is of type text:

select current_setting('mycustom.list', true);

 current_setting
-----------------
 {2,6,1,3,10,8}
(1 row)

However, you can easily convert it to set of rows:

select * 
from unnest(current_setting('mycustom.list', true)::int[])

 unnest
--------
      2
      6
      1
      3
     10
      8
(6 rows)

PostgreSQL: how to get & set an enumerated list variable in a script?

2 Answers2