Declaring variable for a whole script

Question

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.

Answer 1

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

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:

• Assign the result of current_setting('the_setting') to a variable in PL/pgSQL

There are a number of other workarounds, too:

• Is there a way to define a named constant in a PostgreSQL query?

Answer 2

If your script will be loaded via psql, you can \set variable in psql scripts:

\set myvar myvalue
select :'myvar' as myval;

These variables can also be set when invoking psql, by using the --variable/-v/--set option.

---

It is nowadays also possible to have the customized options mentioned by @erwin-brandstetter persist across database settings. These can be set on the database, role or database+role level:

ALTER DATABASE mydb
    SET app.settings.bla = 1::text;
ALTER ROLE myrole
    IN DATABASE mydb
    SET app.settings.bla = 2::text;

Do note that if you work with database-level settings, these will be set as session defaults when the user connects to that database, but can be overridden for that session (or a transaction).

If you wish to enforce a database se, you can do so by going directly to the pg_db_role_setting system catalog, as done by this function from my pg_safer_settings extension (copied here with my explicit permission 😉):

create function pg_db_setting(pg_setting_name$ text, pg_role$ regrole = 0)
    returns text
    stable
--    security definer
    return (
        select
            regexp_replace(expanded_settings.raw_setting, E'^[^=]+=', '')
        from
            pg_catalog.pg_db_role_setting
        inner join
            pg_catalog.pg_database
            on pg_database.oid = pg_db_role_setting.setdatabase
        cross join lateral
            unnest(pg_db_role_setting.setconfig) as expanded_settings(raw_setting)
        where
            pg_database.datname = current_database()
            and pg_db_role_setting.setrole = coalesce(
                pg_role$,
                0  -- 0 means “not role-specific”
            )
            and expanded_settings.raw_setting like pg_setting_name$ || '=%'
        limit 1
    );

Here's an example to illustrate the difference between the semantics of the pg_catalog.current_setting() function and my pg_db_role_setting() function:

CREATE DATABASE mydb;
CONNECT TO mydb
CREATE ROLE myrole;
ALTER DATABASE mydb
    SET app.settings.bla = 1::text;
ALTER ROLE myrole
    IN DATABASE mydb
    SET app.settings.bla = 2::text;
SET ROLE myrole;
SET app.settings.bla TO 3::text;
SELECT current_setting('app.settings.bla', true);  -- '3'
SELECT pg_db_role_setting('app.settings.bla');  -- '1'
SELECT pg_db_role_setting('app.settings.bla', current_user);  -- '2'