1

I have a PostgreSQL 10 script for which I would like to declare variables once at the beginning and then use them throughout the script (with possible transactions).

I know I can do:

DO $$
DECLARE test float := 0.1 ;
BEGIN
    < do whatever here>
END $$

but that makes me declare the variables each time I have a BEGIN / END block.

  • I ended modifying my script and have a long DO-BEGIN-END section. Not what I wanted but it works. – Darth Kangooroo Aug 13 '18 at 9:02
5

There are no global variables per se in Postgres. But we have "customized options" that can be streched for the puropose.

SET myvars.test TO '0.1';

Persists for the duration of the session (not just transaction). Use with:

SELECT current_setting('myvars.test')::float;

Be aware that values are stored as text. So you may need to cast as demonstrated.

To avoid exceptions when referencing an unset variable and get NULL instead:

SELECT current_setting('myvars.test', true)::float;

See:

There are a number of other workarounds, too:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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