How do I declare a variable for use in a PostgreSQL 8.3 query?
In MS SQL Server I can do this:
DECLARE @myvar INT;
SET @myvar = 5/
SELECT * FROM somewhere WHERE something = @myvar;
How do I do the same in PostgreSQL? According to the documentation variables are declared simply as "name type;", but this gives me a syntax error:
myvar INTEGER;
Could someone give me an example of the correct syntax?
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) > ...;
I accomplished the same goal by using a WITH clause, it's nowhere near as elegant but can do the same thing. Though for this example it's really overkill. I also don't particularly recommend this.
WITH myconstants (var1, var2) as (
values (5, 'foo')
)
SELECT *
FROM somewhere, myconstants
WHERE something = var1
OR something_else = var2;
\set as suggested in Shahriar Aghajani's answer. –
Owades JOIN myconstants ON true and then there is no need to do the sub-select. –
Derwon WITH CTE across queries in a transaction. –
Hyposthenia WITH constants AS (SELECT 5 AS var) SELECT * FROM somewhere CROSS JOIN constants WHERE someting=var;. The CROSS JOIN, being a with a single-row table expression, virtually duplicates the data for all of the rows in the real table, and simplifies the expression. –
Pyrrhic 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) > ...;
You could also try this in PLPGSQL:
DO $$
DECLARE myvar integer;
BEGIN
SELECT 5 INTO myvar;
DROP TABLE IF EXISTS tmp_table;
CREATE TEMPORARY TABLE tmp_table AS
SELECT * FROM yourtable WHERE id = myvar;
END $$;
SELECT * FROM tmp_table;
The above requires Postgres 9.0 or later.
you can "abuse" dynamic config settings for this:
-- choose some prefix that is unlikely to be used by postgres
set session my.vars.id = '1';
select *
from person
where id = current_setting('my.vars.id')::int;
Config settings are always varchar values, so you need to cast them to the correct data type when using them. This works with any SQL client whereas \set only works in psql
The above requires Postgres 9.2 or later.
For previous versions, the variable had to be declared in postgresql.conf prior to being used, so it limited its usability somewhat. Actually not the variable completely, but the config "class" which is essentially the prefix. But once the prefix was defined, any variable could be used without changing postgresql.conf
set session my.vars.id = '1'; to set session my.user.id = '1'; will yield ERROR: syntax error at or near "user" –
Tuchman SET LOCAL .... The session variable will be in effect as long as you connection is. The local is scoped to transaction. –
Nirvana set session "my.user.id" = '1'; The current_setting('my.user.id') call works as expected. –
Dziggetai SET SESSION "vars.tomorrow" = CURRENT_DATE + '1 DAY'::interval; does not work, even with casting to text. In my opinion a great restriction. –
Distal current_setting('my.vars.cutoff_date')::timestamp took 130-150 ms, but the same query with a string for the date took 25 ms. –
Randazzo It depends on your client.
However, if you're using the psql client, then you can use the following:
my_db=> \set myvar 5
my_db=> SELECT :myvar + 1 AS my_var_plus_1;
my_var_plus_1
---------------
6
If you are using text variables you need to quote.
\set myvar 'sometextvalue'
select * from sometable where name = :'myvar';
\set must be lowercase –
Andra db=> \set someid 8292 db=> SELECT * FROM sometable WHERE id = :someid; –
Fagin :variable syntax is also what you need to make native queries with parameters via Value.NativeQuery in PowerQuery. –
Oblique This solution is based on the one proposed by fei0x but it has the advantages that there is no need to join the value list of constants in the query and constants can be easily listed at the start of the query. It also works in recursive queries.
Basically, every constant is a single-value table declared in a WITH clause which can then be called anywhere in the remaining part of the query.
WITH
constant_1_str AS (VALUES ('Hello World')),
constant_2_int AS (VALUES (100))
SELECT *
FROM some_table
WHERE table_column = (table constant_1_str)
LIMIT (table constant_2_int)
Alternatively you can use SELECT * FROM constant_name instead of TABLE constant_name which might not be valid for other query languages different to postgresql.
Outside of using pl/pgsql or other pl/* language as suggested, this is the only other possibility I could think of.
begin;
select 5::int as var into temp table myvar;
select *
from somewhere s, myvar v
where s.something = v.var;
commit;
I want to propose an improvement to @DarioBarrionuevo's answer, to make it simpler leveraging temporary tables.
DO $$
DECLARE myvar integer = 5;
BEGIN
CREATE TEMP TABLE tmp_table ON COMMIT DROP AS
-- put here your query with variables:
SELECT *
FROM yourtable
WHERE id = myvar;
END $$;
SELECT * FROM tmp_table;
1 (presumably the row count) rather than the contents of tmp_table. –
Martita As you will have gathered from the other answers, PostgreSQL doesn’t have this mechanism in straight SQL, though you can now use an anonymous block. However, you can do something similar with a Common Table Expression (CTE):
WITH vars AS (
SELECT 5 AS myvar
)
SELECT *
FROM somewhere,vars
WHERE something = vars.myvar;
You can, of course, have as many variables as you like, and they can also be derived. For example:
WITH vars AS (
SELECT
'1980-01-01'::date AS start,
'1999-12-31'::date AS end,
(SELECT avg(height) FROM customers) AS avg_height
)
SELECT *
FROM customers,vars
WHERE (dob BETWEEN vars.start AND vars.end) AND height<vars.avg_height;
The process is:
SELECT without a table (in Oracle you will need to include FROM DUAL).CROSS JOIN syntax, the older comma syntax is slightly more readable.SELECT clause. I used PostgreSQL’s shorter syntax, but you could have used the more formal CAST('1980-01-01' AS date) for cross-dialect compatibility.Normally, you want to avoid cross joins, but since you’re only cross joining a single row, this has the effect of simply widening the table with the variable data.
In many cases, you don’t need to include the vars. prefix if the names don’t clash with the names in the other table. I include it here to make the point clear.
Also, you can go on to add more CTEs.
This also works in all current versions of MSSQL and MySQL, which do support variables, as well as SQLite which doesn’t, and Oracle which sort of does and sort of doesn’t.
You may resort to tool special features. Like for DBeaver own proprietary syntax:
@set name = 'me'
SELECT :name;
SELECT ${name};
DELETE FROM book b
WHERE b.author_id IN (SELECT a.id FROM author AS a WHERE a.name = :name);
@set others = 'Jaques Pierre', 'Juan Pérez' ...WHERE a.name IN (:others)... but @set works on a single line, so long lists become hard to read/edit. You can also add them through the GUI, but then you can't save them with your .sql script. Don't place a semicolon at the end of @set lines, or it will be included in the variable. The docs don't mention lists at all: dbeaver.com/docs/dbeaver/SQL-Execution/… –
Romo True, there is no vivid and unambiguous way to declare a single-value variable, what you can do is
with myVar as (select "any value really")
then, to get access to the value stored in this construction, you do
(select * from myVar)
for example
with var as (select 123)
... where id = (select * from var)
WITH var AS (SELECT SUM(qty) FROM table1 WHERE someval = 12) SELECT id, CASE WHEN (SELECT * FROM var) < 3 THEN 'small' WHEN (SELECT * FROM var) < 10 THEN 'medium' ELSE 'large' END AS "size" FROM table2 WHERE this = that; –
Hilary Here is an example using PREPARE statements. You still can't use ?, but you can use $n notation:
PREPARE foo(integer) AS
SELECT *
FROM somewhere
WHERE something = $1;
EXECUTE foo(5);
DEALLOCATE foo;
In DBeaver you can use parameters in queries just like you can from code, so this will work:
SELECT *
FROM somewhere
WHERE something = :myvar
When you run the query DBeaver will ask you for the value for :myvar and run the query.
Here is a code segment using plain variable in postges terminal. I have used it a few times. But need to figure a better way. Here I am working with string variable. Working with integer variable, you don't need the triple quote. Triple quote becomes single quote at query time; otherwise you got syntax error. There might be a way to eliminate the need of triple quote when working with string variables. Please update if you find a way to improve.
\set strainname '''B.1.1.7'''
select *
from covid19strain
where name = :strainname ;
In psql, you can use these 'variables' as macros. Note that they get "evaluated" every time they are used, rather than at the time that they are "set".
Simple example:
\set my_random '(SELECT random())'
select :my_random; -- gives 0.23330629315990592
select :my_random; -- gives 0.67458399344433542
this gives two different answers each time.
However, you can still use these as a valuable shorthand to avoid repeating lots of subselects.
\set the_id '(SELECT id FROM table_1 WHERE name = ''xxx'' LIMIT 1)'
and then use it in your queries later as
:the_id
e.g.
INSERT INTO table2 (table1_id,x,y,z) VALUES (:the_id, 1,2,3)
Note you have to double-quote the strings in the variables, because the whole thing is then string-interpolated (i.e. macro-expanded) into your query.
For example, you can set the custom options my.num and my.first.name as variables as shown below. *A custom option can have multiple . and custom options are deleted after logout:
SET my.num = 2;
SET my.first.name = 'John';
Or, you can set the custom options with set_config() as shown below. *For set_config(), false in the 3rd argument means the new value applies to the current session and true in the 3rd argument means the new value applies to the current transaction and my answer explains set_config() in detail:
SELECT set_config('my.num', '2', false);
SELECT set_config('my.first.name', 'John', false);
Then, you must use current_setting() to get the values of the custom options my.num and my.first.name as shown below:
postgres=# SELECT current_setting('my.num');
current_setting
-----------------
2
(1 row)
postgres=# SELECT current_setting('my.first.name');
current_setting
-----------------
John
(1 row)
Be careful, setting a custom option without . gets the error as shown below:
postgres=# SET num = 2;
ERROR: unrecognized configuration parameter "num"
Or:
postgres=# SELECT set_config('num', '2', false);
ERROR: unrecognized configuration parameter "num"
And, 2 without '' in set_config() gets the error as shown below:
postgres=# SELECT set_config('my.num', 2, false);
ERROR: function set_config(unknown, integer, boolean) does not exist
LINE 1: SELECT set_config('my.num', 2, false);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
And, using a custom option without current_setting() cannot get the value as shown below:
postgres=# SELECT 'my.num';
?column?
----------
my.num
(1 row)
And, using a custom option without '' and current_setting() gets the error as shown below:
postgres=# SELECT my.num;
ERROR: missing FROM-clause entry for table "my"
LINE 1: SELECT my.num;
Next for example, you can use \set to set the number 2 to num as shown below. *You must use : to access num according to the doc otherwise there is error and num is removed after logout:
postgres=# \set num 2
postgres=# SELECT :num;
?column?
----------
2
(1 row)
And, you can use \set to set the text John Smith with '' to name as shown below. *Don't use "" for John Smith because "" is included in the text so the output is "John Smith" and you must use '' for name otherwise there is error and name is removed after logout:
postgres=# \set name 'John Smith'
postgres=# SELECT :'name';
?column?
------------
John Smith
(1 row)
Be careful, setting the text John Smith without '' to name removes the space between John and Smith as shown below:
postgres=# \set name John Smith
postgres=# SELECT :'name';
?column?
-----------
JohnSmith
(1 row)
And, only using \set can show all variables as shown below:
postgres=# \set
AUTOCOMMIT = 'on'
COMP_KEYWORD_CASE = 'preserve-upper'
...
name = 'John Smith'
num = '2'
And, you can use \unset to unset(remove) num and name as shown below:
postgres=# \unset num
postgres=# \unset name
In addition, you can use \echo to output num as shown below:
postgres=# \set num 2
postgres=# \echo :num
2
And, you can use \echo to output name as shown below. *Don't use '' for name because '' is included in the text so the output is 'John Smith':
postgres=# \set name 'John Smith'
postgres=# \echo :name
John Smith
Using fei0x's answer and bluish's suggestion you can combine them to store your values in a TEMP table. Following is a snippet if someone wants to use the date/timestamp to save into the variables.
drop table if exists tempdb_var;
CREATE TEMP TABLE tempdb_var as
with varInit (v_startdate, v_enddate) as (
values(CURRENT_DATE - '1 day'::interval, CURRENT_DATE + '1 day'::interval)
)
select a.* from varInit a;
select v_startdate,v_enddate from tempdb_var;
You can use the variables v_startdate and v_enddate in any query.
© 2022 - 2024 — McMap. All rights reserved.