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

Question

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;

Answer 1

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.

Answer 2

PostgreSQL has no built-in way to define (global) variables like MySQL or Oracle. (There is a limited workaround using "customized options"). Depending on what you want exactly there are other ways:

For one query

You can provide values at the top of a query in a CTE like @Gordon already provided.

Global, persistent constant:

You could create a simple IMMUTABLE function for that:

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

(Parallel safety settings only apply to Postgres 9.6 or later.)

It has to live in a schema that is visible to the current user, i.e. is in the respective search_path. Like the schema public, by default. If security is an issue, make sure it's the first schema in the search_path or schema-qualify it in your call:

SELECT public.f_myid();

Visible for all users in the database (that are allowed to access schema public).

Multiple values for current session:

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'

Since plpgsql 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 val(val_id, val) VALUES (2, 'wrong');

SET search_path = myschema, 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 manual on creating functions with SECURITY DEFINER.

A hard-wired schema is typically simpler and faster:

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

Related answers with more options:

• How to test my ad-hoc SQL with parameters in Postgres query window

• Passing user id to PostgreSQL triggers

Answer 3

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.

Answer 4

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)

Answer 5

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

Answer 6

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

Answer 7

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.