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!

Answer 1

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