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

Asked 9/11, 2012 at 22:10 Answered 28/2, 2023 at 11:48

Is there a way to define a named constant in a PostgreSQL query? For example:

MY_ID = 5;
SELECT * FROM users WHERE id = MY_ID;

Acotyledon answered 9/11, 2012 at 22:10 Comment(3)

How about a bit more context? Are you playing around in psql and trying to avoid remembering something or computing something over and over again? Or are you writing an SQL script for something? – Mccahill 9/11, 2012 at 22:27

What's the scope supposed to be? Transaction? Session? For one user / all users / one DB / all DBs in cluster? – Probationer 9/11, 2012 at 22:30

I'm really just looking for an easy way to make my SQL Query easier to change in the future. Instead of putting 5 in a bunch of places all over the query, I'd rather just define MY_ID and then change that later if I need to. – Acotyledon 13/11, 2012 at 17:23

This question has been asked before (How do you use script variables in PostgreSQL?). However, there is a trick that I use for queries sometimes:

with const as (
    select 1 as val
)
select . . .
from const cross join
     <more tables>

That is, I define a CTE called const that has the constants defined there. I can then cross join this into my query, any number of times at any level. I have found this particularly useful when I'm dealing with dates, and need to handle date constants across many subqueries.

Hoberthobey answered 9/11, 2012 at 22:58 Comment(7)

This works. But is it really the best there is in Postgres? I'm using a read-only db so I can't write functions. If I use this solution, I'll have to write many cross joins for complex queries. I just want to set a variable and forget! – Deuteranopia 22/6, 2018 at 14:2

@Deuteranopia . . . The cross join shouldn't affect the performance. The CTE has only one row. – Hoberthobey 23/6, 2018 at 1:49

How to use this method for a list of constants. Something like: ``` with const as ( (1,4,3,6,517) as vals ) ``` I tried this but it did not work though. – Overshine 20/3, 2020 at 0:3

@Overshine . . . with const as (select 1 as val1, 4 as val2, . . . ). – Hoberthobey 20/3, 2020 at 0:5

Thanks @GordonLinoff. That won't work for me because the usecase requires a single name for the whole list, so that the list can be used in the main query like: select ... where foobar in (select vals from const) – Overshine 20/3, 2020 at 0:24

@Overshine try this:

WITH const(vals) AS (SELECT * FROM unnest(ARRAY[1,2,3])) SELECT ... FROM ... CROSS JOIN const WHERE ... IN (SELECT vals FROM const)

– Alisaalisan 13/8, 2021 at 16:54

I couldn't understand why the cross join is necessary. Adding the consts to the end wouldn't achieve the same result? – Murielmurielle 7/10, 2022 at 20:11

PostgreSQL has a limited built-in way to define (global) variables using "customized options". See:

User-defined variables in PostgreSQL

The limitations being that the name has to be qualified (must contain a dot), and only string values (type text) are stored and returned.

Depending on what you want exactly there are other ways:

Global, persistent constants

Create simple IMMUTABLE, LANGUAGE sql functions. Those are visible to all sessions in the same database, for all roles with sufficient privileges. These can be inlined by the Postgres query planner, making them actual constant values.

CREATE FUNCTION public.f_myid()
  RETURNS int
  LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT 5';

(Parallel safety setting applies to Postgres 9.6 or later.)

For ease of use, place such functions in a schema that is visible to all executing roles. I.e., the schema must be included in their search_path. Like the schema public, by default. If security might be an issue, schema-qualify function calls: (Else you can omit the schema.)

SELECT public.f_myid();

Multiple values for the current session

Consider a temporary table, optionally with a function on top:

CREATE TEMP TABLE val (val_id int PRIMARY KEY, val text);

INSERT INTO val(val_id, val) VALUES
  (  1, 'foo')
, (  2, 'bar')
, (317, 'baz')
;

CREATE FUNCTION f_val(_id int)
  RETURNS text
  LANGUAGE sql STABLE PARALLEL RESTRICTED AS
'SELECT val FROM val WHERE val_id = $1';

SELECT f_val(2);  -- returns 'baz'

Either column can be any data type.
Access to temporary tables is PARALLEL RESTRICTED.

Since PL/pgSQL checks the existence of a table on creation, you need to create a (temporary) table val before you can create the function - even if a temp table is dropped at the end of the session while the function persists. The function will raise an exception if the underlying table is not found at call time.

The current schema for temporary objects comes before the rest of your search_path per default - if not instructed otherwise explicitly. You cannot exclude the temporary schema from the search_path, but you can put other schemas first.
Evil creatures of the night (with the necessary privileges) might tinker with the search_path and put another object of the same name in front:

CREATE TABLE myschema.val (val_id int PRIMARY KEY, val text);

INSERT INTO myschema.val(val_id, val) VALUES (2, 'wrong');

SET search_path = myschema, public, pg_temp;

SELECT f_val(2);  -- returns 'wrong' ❌

It's not much of a threat, since only privileged users can alter global settings. Other users can only do it for their own session. Consider the related chapter of the manual on creating functions with SECURITY DEFINER.

A hard-wired schema in function and call is safe (always overrules the search_path), and typically simpler and faster:

CREATE FUNCTION public.f_val(_id int)
  RETURNS text
  LANGUAGE sql STABLE PARALLEL RESTRICTED AS
'SELECT val FROM pg_temp.val WHERE val_id = $1';

SELECT public.f_val(2);  -- returns 'bar' ✅

Of course, the same works with a regular table (in a regular schema), too. Then the function can be PARALLEL SAFE.
Or you use a regular table as global template, and overlay with a temp table per session. Very flexible. But back to PARALLEL RESTRICTED again.

For a single query

Provide values at the top of a query in a CTE like @Gordon provided.

Related answers with more options:

Probationer answered 9/11, 2012 at 23:40 Comment(0)

In addition to the sensible options Gordon and Erwin already mentioned (temp tables, constant-returning functions, CTEs, etc), you can also (ab)use the PostgreSQL GUC mechanism to create global-, session- and transaction-level variables.

See this prior post which shows the approach in detail.

I don't recommend this for general use, but it could be useful in narrow cases like the one mentioned in the linked question, where the poster wanted a way to provide the application-level username to triggers and functions.

Conflagration answered 10/11, 2012 at 3:7 Comment(0)

I've found this solution:

with vars as (
    SELECT * FROM (values(5)) as t(MY_ID)
)
SELECT * FROM users WHERE id = (SELECT MY_ID FROM vars)

Eliathan answered 4/5, 2017 at 14:27 Comment(1)

with vars as (SELECT * FROM (values(5)) as t(MY_ID) ) can be simplified to with vars (my_id) as (values(5)) – Coeternity 4/5, 2017 at 15:2

I've found a mixture of the available approaches to be best:

Store your variables in a table:

CREATE TABLE vars (
  id INT NOT NULL PRIMARY KEY DEFAULT 1,
  zipcode INT NOT NULL DEFAULT 90210,
  -- etc..
  CHECK (id = 1)
);

Create a dynamic function, which loads the contents of your table, and uses it to:
- Re/Create another separate static immutable getter function.

CREATE FUNCTION generate_var_getter()
RETURNS VOID AS $$
DECLARE
  var_name TEXT;
  var_value TEXT;
  new_rows TEXT[];
  new_sql TEXT;
BEGIN
  FOR var_name IN (
    SELECT columns.column_name
    FROM information_schema.columns
    WHERE columns.table_schema = 'public'
      AND columns.table_name = 'vars'
    ORDER BY columns.ordinal_position ASC
  ) LOOP
    EXECUTE
      FORMAT('SELECT %I FROM vars LIMIT 1', var_name)
      INTO var_value;

    new_rows := ARRAY_APPEND(
      new_rows,
      FORMAT('(''%s'', %s)', var_name, var_value)
    );
  END LOOP;

  new_sql := FORMAT($sql$
    CREATE OR REPLACE FUNCTION var_get(key_in TEXT)
    RETURNS TEXT AS $config$
    DECLARE
      result NUMERIC;
    BEGIN
      result := (
        SELECT value FROM (VALUES %s)
        AS vars_tmp (key, value)
        WHERE key = key_in
      );
      RETURN result;
    END;
    $config$ LANGUAGE plpgsql IMMUTABLE;
  $sql$, ARRAY_TO_STRING(new_rows, ','));

  EXECUTE new_sql;
  RETURN;
END;
$$ LANGUAGE plpgsql;

Add an update trigger to your table, so that after you change one of your variables, generate_var_getter() is called, and the immutable var_get() function is recreated.

CREATE FUNCTION vars_regenerate_update()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM generate_var_getter();
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_vars_regenerate_change
  AFTER INSERT OR UPDATE ON vars
  EXECUTE FUNCTION vars_regenerate_update();

Now you can easily keep your variables in a table, but also get blazing-fast immutable access to them. The best of both worlds:

INSERT INTO vars DEFAULT VALUES;
-- INSERT 0 1

SELECT var_get('zipcode')::INT; 
-- 90210

UPDATE vars SET zipcode = 84111;
-- UPDATE 1

SELECT var_get('zipcode')::INT;
-- 84111

Buddhology answered 23/1, 2019 at 8:18 Comment(2)

Very nice. For production use, we could also add suitable grants to ensure that the vars table is not updated accidentally. Or perhaps implement a read-only flag for constants that are meant to be fixed at design time - that would require a multi-row vars table though. – Dkl 10/5, 2019 at 1:32

Nice! You might be interested to know that I've stumbled upon a very pattern a while ago and put the requisite trigger into a PostgreSQL extension: github.com/bigsmoke/pg_safer_settings The main difference between what your code does and my pg_safer_settings_table is that the latter makes a function to get the current configuration value for each configuration column. (By default, these functions are called current_<col_name>().) – Giralda 28/2, 2023 at 11:31

When your query uses "GROUP BY":

WITH const AS (
     select 5                 as MY_ID,
            '2022-03-1'::date as MY_DAY)

SELECT u.user_group,
       COUNT(*),
       const.MY_DAY
FROM users u
CROSS JOIN const

WHERE 1=1
GROUP BY u.user_group, const.MY_ID, const.MY_DAY

the sample contains more fields, than the OP, but that helps to more visitors, who are looking for the subject.

Without GROUP BY:

WITH const AS (
     select 5 as MY_ID)

SELECT u.* FROM users u
CROSS JOIN const

WHERE u.id = const.MY_ID

credits to @GordonLinoff

Without GROUP BY and no column-name conflicts:

WITH const AS (
     select 5 as MY_ID)

SELECT users.* FROM users
CROSS JOIN const

WHERE id = MY_ID

Reynaldoreynard answered 5/4, 2022 at 17:43 Comment(0)

If you want to persist your constant across sessions and you don't want to use a table, you can set custom settings for a specific database or role. Such settings can be overridden at the session or transaction level. But that's easy to read around by going directly to the pg_db_role_settings system catalog:

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
    );

This function is copy-pasted (with permission 😉) from my pg_safer_settings extension.

Here's an example, taken from the pg_safer_settings README) of how to use it:

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'

If you prefer, the test routine is a bit more elaborate:

CREATE OR REPLACE PROCEDURE ext.test__pg_db_setting()
 LANGUAGE plpgsql
 SET "plpgsql.check_asserts" TO 'true'
 SET "pg_readme.include_this_routine_definition" TO 'true'
AS $procedure$
begin
    execute 'ALTER DATABASE ' || current_database()
        || ' SET pg_safer_settings.test_pg_db_setting = ''foo''';
    assert pg_db_setting('pg_safer_settings.test_pg_db_setting') = 'foo';

    set pg_safer_settings.settings.test_pg_db_setting = 'bar';
    assert pg_db_setting('pg_safer_settings.test_pg_db_setting') = 'foo';

    assert pg_db_setting('pg_safer_settings.unknown_setting') is null;

    create role __test_role;
    execute 'ALTER ROLE __test_role IN DATABASE ' || current_database()
        || ' SET pg_safer_settings.test_pg_db_setting = ''foobar''';
    assert pg_db_setting('pg_safer_settings.test_pg_db_setting', '__test_role') = 'foobar';
    assert pg_db_setting('pg_safer_settings.test_pg_db_setting') = 'foo';

    raise transaction_rollback;
exception
    when transaction_rollback then
end;
$procedure$

pg_safer_settings also provides a more sophisticated variation of the pattern proposed by @Brev his answer.

Giralda answered 28/2, 2023 at 11:48 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++