How to return values from psql to bash with custom session settings

Question

I need to set the datestyle to "dd/mm/yyyy" before calling a procedure and getting it's return value back to bash.
I tried the following:

read return <<< $(psql \
                  -x \
                  -U postgres \
                  -d MY_DB \
                  --quiet`\
                  -t \
                  -c "SELECT CURRENT_TIMESTAMP")    //just to make sure the date format is correct 
echo ${return[0]} | cut -d'|' -f 2

I'm getting back a normal date format yyyy-mm-dd.

Erwin Brandstetter · Answer 1 · 2022-04-08T00:19:24.973

First off, if the output format matters, rather use to_char() instead of depending on the datestyle setting. Like:

SELECT to_char(current_timestamp, 'DD/MM/YYYY');

But to answer your question:

I need to set the datestyle to "dd/mm/yyyy"

"dd/mm/yyyy" isn't a valid setting, this would do it:

datestyle = 'SQL, DMY'

The datestyle setting is a bit odd for historical reasons. The manual:

In the SQL and POSTGRES styles, day appears before month if DMY field ordering has been specified, otherwise month appears before day.

Also, I suggest multiple, separate -c options for psql:

postgres@test:~$ read return <<< $(psql \
>                   -x \
>                   -U postgres \
>                   -d MY_DB \
>                   --quiet \
>                   -t \
>                   -c "SET datestyle = 'SQL, DMY'" \
>                   -c 'SELECT CURRENT_TIMESTAMP')
postgres@test:~$ echo ${return[0]} | cut -d'|' -f 2
 08/11/2021 22:37:11.915721 CET

You mentioned:

before calling a procedure

Unless you actually mean "function" and just use the wrong term (a widespread misnomer), a single -c option, as has been suggested, does not work if the PROCEDURE uses any transaction control commands (COMMIT, ROLLBACK, ...). And those are the main reason to use a procedure rather than a function in the first place. See:

In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?

The manual on Transaction Management:

Transaction control is only possible in CALL or DO invocations from the top level or nested CALL or DO invocations without any other intervening command.

The manual for psql:

Each SQL command string passed to -c is sent to the server as a single request. Because of this, the server executes it as a single transaction even if the string contains multiple SQL commands, unless there are explicit BEGIN/COMMIT commands included in the string to divide it into multiple transactions. (See Section 53.2.2.1 for more details about how the server handles multi-query strings.) Also, psql only prints the result of the last SQL command in the string. This is different from the behavior when the same string is read from a file or fed to psql's standard input, because then psql sends each SQL command separately.

Because of this behavior, putting more than one SQL command in a single -c string often has unexpected results. It's better to use repeated -c commands or feed multiple commands to psql's standard input, either using echo as illustrated above, or via a shell here-document ...

Bold emphasis mine.

So while this works:

 -c "SET datestyle = 'SQL, DMY'; SELECT current_timestamp;"

This doesn't (if the procedure uses transaction control commands):

 -c "SET datestyle = 'SQL, DMY'; CALL my_procedure();"

You'd get this error:

ERROR: invalid transaction termination

How to return values from psql to bash with custom session settings

1 Answers1