How to declare a variable in a PostgreSQL query

Question

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

User · Answer

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

User · Answer

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

User · Answer

Dynamic Config Settings  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

User · Answer

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     Basic example with two constants    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

User · Answer

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

User · Answer

It depends on your client   However  if you re using the psql client  then you can use the following   my db  gt   set myvar 5 my db  gt  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

User · Answer

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

User · Answer

Using a Temp Table outside of pl PgSQL  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

User · Answer

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

User · Answer

You could also try this in PLPGSQL   DO    DECLARE myvar integer  BEGIN     SELECT 5 INTO myvar       DROP TABLE IF EXISTS tmp table      CREATE TABLE tmp table AS     SELECT   FROM yourtable WHERE   id   myvar  END      SELECT   FROM tmp table    The above requires Postgres 9 0 or later

User · Answer

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   gt

User · Answer

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

[sql] How to declare a variable in a PostgreSQL query

Examples related to sql

Examples related to postgresql

Examples related to postgresql-8.3