PostgreSQL PSQL SELECT into a variable

Question

On the Postgres console (PSQL) I try to write a simple 'batch' test:

\set oid

\set username 'john'
\set emailadr '[email protected]'
\set password 'jjjj'
INSERT INTO :oid SELECT auc_user_insert(:'username', :'emailadr', :'password');
SELECT auc_user_select(:oid);
SELECT auc_user_delete(:oid);

auc_user_insert, auc_user_select and auc_user_delete are application specific Postgres functions and work fine for each self (within the Query Tool). The insert Function returns a bigint as unique id, which I would like to assign to the variable oid for usage in the next lines.

But in PSQL for the INTO :oid statement I get the error

ERROR:  Relation 'bigint' doesn't exist

What's wrong and what would be the right way to work with such a variable? Thanks for any hint!

Daniel Vérité · Accepted Answer · 2015-02-03 17:01:40Z

7

Assuming the INSERT INTO is meant to affect the :oid variable rather than inserting the result of a select into a table, you want this instead:

SELECT auc_user_insert(:'username', :'emailadr', :'password') AS oid;
\gset
SELECT auc_user_select(:oid);
SELECT auc_user_delete(:oid);

\gset is a new meta-command since psql 9.3

If you have an older version, it's much harder to emulate this.

Description from the manpage:

\gset [ prefix ]

Sends the current query input buffer to the server and stores the query's output into psql variables (see Variables). The query to be executed must return exactly one row. Each column of the row is stored into a separate variable, named the same as the column

answered Feb 3, 2015 at 17:01

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL PSQL SELECT into a variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related