Thread: Setting pgsql variable from query result
Hello,
I need to write a DB init script that creates a couple of basic objects in my DB. I need to be able to temporarily store several sequence values so that I can use all of them in my queries later on in the script. Like so (simplified example):
\set fn_id "select nextval('SEQ_LOCALIZED_STR')"
\set sn_id "select nextval('SEQ_LOCALIZED_STR')"
....
other nextvals from the same sequence
insert into PERSON ( ID, FIRST_NAME_LS, SURNAME_LS, ...) values ( nextval('SEQ_PERSON'), :fn_id, :sn_id );
I left out all the quote escaping stuff. Obviously the above does not work because the variables are initialized with the "select..." string literals rather then the sequence values.
Hence my question, is it posible to set a variable from a query result? Or can I use some other mechanism to keep multiple interim sequence values and then use these values in a query. If possible I would like to avoid setting the variable by running external pgsql process like so:
\set fn_id `pgsql -U user -P passw -c "select nextval('SEQ_LOCALIZED_STR')" mydb` - syntax may be incorrect
Thank you,
Jan Moravec