1

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.

2
  • 1
    Why can't you just use -c "set datestyle = 'sql, dmy';select current_timestamp;" argument?
    – peremeykin
    Nov 7, 2021 at 13:29
  • @peremeykin: That might not work with a procedure. Nov 8, 2021 at 22:19

1 Answer 1

1

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:

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

3
  • Thanks for the extensive repy, what I'm trying to do is exactly this, set datestyle to SQL,DMY sorry if it isn't in the question, and then call a procedure -that has transacion controls- but for some reason needs to check if the date format is dd/mm/yyyy because client!! or else it stops execution, so yeah, i'm trying -c "SET datestyle = 'SQL, DMY'; CALL my_procedure();" and it' not failing, but i'm still not geting the dateformat that i want, somehow either it's getting ignored, or it's being reset, thank you
    – Majd
    Nov 9, 2021 at 9:23
  • If the procedure has transaction control commands, this call should certainly fail: -c "SET datestyle = 'SQL, DMY'; CALL my_procedure();" It fails for me, testing with current Postgres 14. Nov 9, 2021 at 15:25
  • @Majd: So is this question answered properly? Dec 6, 2021 at 23:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.