How to get the variables of the select field in the psql client of PostgreSQL

user_4758376 0
Viewed 69

in psql client: run

select pg_backend_pid() ospid \gset

\echo :ospid

this can get ospid value;

but : I want echo ospid col value to file

\! echo 'a' :ospid 'b' > /tmp/test.log

how can i fix it ? thanks.

psql postgresql
2 Answers

As the documentation says:

Unlike most other meta-commands, the entire remainder of the line is always taken to be the argument(s) of \!, and neither variable interpolation nor backquote expansion are performed in the arguments.

You can work around that with \g:

test=> \pset format unaligned
Output format is unaligned.
test=> \pset tuples_only
Tuples only is on.
test=> SELECT pg_backend_pid() ospid \gset
test=> SELECT 'a ' || :ospid  || ' b' \g /tmp/test.log
user_64643080

Thanks @Laurenz Albe.

My goal is to set the terminal title.

I work around with this

\pset tuples_only on
\pset border 0

SELECT user,pg_backend_pid() ospid  \g  ./_ospid.log

\echo '******** ospid:=[':ospid']   user:=[':user']********'

\! echo -ne '\033]0;'`cat ./_ospid.log`'\007' ;


\pset tuples_only off
\pset border 2
user_47583760
Related