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

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.


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


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.

Tags:

Sql

Postgresql

Recent Posts