How to Define a Named Constant in a Postgresql Query

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

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
     

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.

Is there a way to define a named constant/parameter in a single-statement SQL query?

You need to have dates in the FROM clause if you want it in the query. You can do this as:

WITH dates as (SELECT '2015-01-01' as start, '2016-01-01' as end)
SELECT t.*
FROM my_table t JOIN
     dates d
     ON t.start_date >= d.start AND t.end_date <= d.end;

Note: You can also do this with a CROSS JOIN. I often write queries as:

WITH params as (
     SELECT '2015-01-01' as start, '2016-01-01' as end
    )
SELECT t.*
FROM params CROSS JOIN
     my_table t
WHERE t.start_date >= params.start AND t.end_date <= params.end;

How to store a constant value in a Postgresql script

You can use psql's \gset to set a variable to the result of a query:

SELECT uuid_generate_v4() AS generated_id \gset

That will set the variable generated_id.

To use the variable in your script, quote it like this:

:'generated_id'

Defining global constants in Postgresql stored function/procedures?

Take a look at this other answer. It uses the same approach that you do.

Create constant string for entire database

How to declare a variable in a PostgreSQL query

There is no such feature in PostgreSQL. You can do it only in pl/PgSQL (or other pl/*), but not in plain SQL.

An exception is WITH () query which can work as a variable, or even tuple of variables. It allows you to return a table of temporary values.

WITH master_user AS (
    SELECT
      login,
      registration_date
    FROM users
    WHERE ...
)

SELECT *
FROM users
WHERE master_login = (SELECT login
                      FROM master_user)
      AND (SELECT registration_date
           FROM master_user) > ...;

Declaring the table name in constant in Postgres Stored procedure

Well you can use dynamic SQL, but realize dynamic SQL often adds way more complexity. Good when really needed but should be avoided when possible. The following shows what would be needed for what you want to do. Is not having to type the table name for each SQL statement worth the additional trouble?

create or replace function get_data()
returns void as 
$func$
declare

    table_name_a constant text = 'asp.monitoring_bookmark_original';  

    file_cursor           text = 'select distinct filename,systemuid from %i'; 
    file_ref              refcursor; 
    file_rec              record;

    data_cursor           text =$stmt$select * from %i where filename = '%s' and systemuid= '%s' order by mindatetime, maxdatetime$stmt$;
    data_ref              refcursor; 
    data_rec              record;
begin
    --open the file cursor
    open file_ref for execute format(file_cursor,table_name_a);
    loop 
        fetch next from file_ref into file_rec; 
        exit when not found;

        -- and extending from what the second query inplies
        open data_ref for execute format(data_cursor,table_name_a,file_rec.filename,file_rec.systemid);
        loop 
            fetch next from data_ref into data_rec;
            exit when not found; 
            --//logic goes here
        end loop; 
    end loop ;       
end; 
$func$
language plpgsql;

Create constant string for entire database

Create a tiny function that serves as "global constant":

CREATE OR REPLACE FUNCTION f_my_constant()
  RETURNS text AS
$$SELECT text 'XXX'$$ LANGUAGE sql IMMUTABLE PARALLEL SAFE; -- see below

And use that function instead of 'BLABLABLA' in your queries.

Be sure to declare the data type correctly and make the function IMMUTABLE (because it is) for better performance with big queries.

In Postgres 9.6 or later add PARALLEL SAFE, so it won't block parallel query plans. The setting isn't valid in older versions.

To change the constant, replace the function by running an updated CREATE OR REPLACE FUNCTION statement. Invalidates query plans using it automatically, so queries are re-planned. Should be safe for concurrent use. Transactions starting after the change use the new function. But indexes involving the function have to be rebuilt manually.

---

Alternatively (especially in pg 9.2 or later), you could set a Customized Option as "global constant" for the whole cluster, a given DB, a given role etc, and retrieve the value with:

current_setting('constant.blabla')

One limitation: the value is always text and may have to be cast to a target type.

Related:

• User defined variables in PostgreSQL

Many ways to set it:

• How does the search_path influence identifier resolution and the "current schema"

---

Related Topics