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
-c "set datestyle = 'sql, dmy';select current_timestamp;"argument?