CODEWITHSUNDEEP
postgresqlpsql
num1
num1

Reputation: 4973

How to embed multi-line shell command in psql file

I have a .sql file with some lines which look like this:

\set buf `cat example-message.msg | base4 -w0`
SELECT * FROM run_select(:'buf');

However, this hides the content of example-message.msg and makes my file (which is actually a list of test cases) less readable than I would like. Ideally, I'd be able to in-line the contents of example-message.msg. But it's a large, multi-line file.

I've tried using here-docs:

\set buf `cat <<EOF | base64 -w0
plan { }
EOF
`

However, here psql complains about an unterminated quoted string and in retrospect the docs say it won't work anyway. (But in any case, the arguments of a meta-command cannot continue beyond the end of the line.)

Next I considered somehow building the string which is executed:

SELECT $$
plan: {}
$$ AS rawbuf \gset
\set buf `echo :rawbuf | base64 -w0`

This doesn't work either, for obvious reasons. But I can't seem to get any variation of it to work either. I suppose psql doesn't do variable substitution within backticks.

Is there some other method I'm missing?

Upvotes: 4

Views: 1573

Answers (1)

Daniel V&#233;rit&#233;
Daniel V&#233;rit&#233;

Reputation: 61636

Multi-line meta-commands are not supported indeed. In your case, it seems like it's the round-trip to base64 -w0 that calls for a meta-command. But it looks like you could avoid that by doing the base64 encoding in SQL:

SELECT encode(convert_to($delim$
  --multi-line string
$delim$
, 'UTF-8'), 'base64') AS buf \gset

SELECT * FROM run_select(:'buf');

The temporary :buf could also be avoided if it doesn't have any other use than being reinjected:

SELECT * FROM run_select(encode(convert_to($delim$
  --multi-line string
$delim$
  , 'UTF-8'), 'base64')
);

Upvotes: 3

Related Questions