PostgreSQL script control structures

Question

I have an sql script which copies data from a file:

--myscript.sql
\set file :dir 'data.csv'
copy data from :'file' csv;

and execute it with psql providing the dir variable

psql -v dir="D:\data\" -f myscript.sql

now I would like the copy command executed only if some other variable is let, e.g. -v doit=

Are there any script control structures available for this? Looking for something like

$if :{?doit}
copy data from :'file' csv;
$endif;

Have tried to wrap it by an anonymous block

do '
begin
    if ':{?doit}' then
        copy data from :'file' csv;
    end if;
end';

But it gives the error

Error: syntax error (approximate position "TRUE")
LINE 3:  if 'TRUE' then
             ^

I found related post. https://stackoverflow.com/questions/28997527/use-variable-set-by-psql-meta-command-inside-of-do-block — jian, Jun 13 '22 at 08:41
This solution https://stackoverflow.com/a/29000817/5360898 solved it perfectly. Thanks for the directions! — yaugenka, Jun 13 '22 at 10:15

score 0 · Answer 1 · answered Jun 13 '22 at 14:03

Answering my own question.

There were two issues there

psql variables cannot be directly substituted in do statements This can be solved by putting a psql variable into a server setting as suggested here
the copy command does not accept any functions for the file path name, so the first solution won't work here.

I ended up formating the do block like this

select format('
begin
    if ''%s''=''t'' then
        copy rates from ''%s'' csv header delimiter E''\t'';
    end if;
end;',:{?doit},:'file') as do \gset
do :'do';

The format function lets us use a multiline string. The resulting string is then assigned to a new varialbe with the help of \gset and feed it to do.

Note: The format function formats 'TRUE' as 't', so we have to treat it as such.

Thanks to @Mark for the directions.

PostgreSQL script control structures

1 Answers1