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.

• Darth Kangooroo: I ended modifying my script and have a long DO-BEGIN-END section. Not what I wanted but it works.

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'