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
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):
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.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)
