Mixing
PostgreSQL: how to get & set an enumerated list variable in a script?
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
postgresql
asked Jan 2 at 18:26
user952342user952342
1,00561749
add a comment |
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
postgresql
asked Jan 2 at 18:26
user952342user952342
1,00561749
add a comment |
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
postgresql
asked Jan 2 at 18:26
user952342user952342
1,00561749
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
postgresql
postgresql
asked Jan 2 at 18:26
user952342user952342
1,00561749
asked Jan 2 at 18:26
user952342user952342
1,00561749
asked Jan 2 at 18:26
user952342user952342
1,00561749
asked Jan 2 at 18:26
user952342user952342
1,00561749
asked Jan 2 at 18:26
user952342user952342
1,00561749
1,00561749
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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)
answered Jan 2 at 18:55
klinklin
60.6k65787
add a comment |
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?
answered Jan 2 at 18:44
Erwin BrandstetterErwin Brandstetter
353k69642821
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 2 at 18:47
Works for me, I tested with Postgres 11. See the added fiddle.
– Erwin Brandstetter
Jan 2 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 2 at 18:51
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54011339%2fpostgresql-how-to-get-set-an-enumerated-list-variable-in-a-script%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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)
answered Jan 2 at 18:55
klinklin
60.6k65787
add a comment |
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)
answered Jan 2 at 18:55
klinklin
60.6k65787
add a comment |
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)
answered Jan 2 at 18:55
klinklin
60.6k65787
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)
answered Jan 2 at 18:55
klinklin
60.6k65787
answered Jan 2 at 18:55
klinklin
60.6k65787
answered Jan 2 at 18:55
klinklin
60.6k65787
answered Jan 2 at 18:55
klinklin
60.6k65787
60.6k65787
add a comment |
add a comment |
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?
answered Jan 2 at 18:44
Erwin BrandstetterErwin Brandstetter
353k69642821
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 2 at 18:47
Works for me, I tested with Postgres 11. See the added fiddle.
– Erwin Brandstetter
Jan 2 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 2 at 18:51
add a comment |
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?
answered Jan 2 at 18:44
Erwin BrandstetterErwin Brandstetter
353k69642821
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 2 at 18:47
Works for me, I tested with Postgres 11. See the added fiddle.
– Erwin Brandstetter
Jan 2 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 2 at 18:51
add a comment |
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?
answered Jan 2 at 18:44
Erwin BrandstetterErwin Brandstetter
353k69642821
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?
answered Jan 2 at 18:44
Erwin BrandstetterErwin Brandstetter
353k69642821
edited Jan 2 at 19:00
answered Jan 2 at 18:44
Erwin BrandstetterErwin Brandstetter
353k69642821
answered Jan 2 at 18:44
Erwin BrandstetterErwin Brandstetter
353k69642821
answered Jan 2 at 18:44
Erwin BrandstetterErwin Brandstetter
353k69642821
353k69642821
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 2 at 18:47
Works for me, I tested with Postgres 11. See the added fiddle.
– Erwin Brandstetter
Jan 2 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 2 at 18:51
add a comment |
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 2 at 18:47
Works for me, I tested with Postgres 11. See the added fiddle.
– Erwin Brandstetter
Jan 2 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 2 at 18:51
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 2 at 18:47
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 2 at 18:47
Works for me, I tested with Postgres 11. See the added fiddle.
– Erwin Brandstetter
Jan 2 at 18:49
Works for me, I tested with Postgres 11. See the added fiddle.
– Erwin Brandstetter
Jan 2 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 2 at 18:51
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 2 at 18:51
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54011339%2fpostgresql-how-to-get-set-an-enumerated-list-variable-in-a-script%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
