How do you use variables in a simple PostgreSQL script?

Question

For example, in MS-SQL, you can open up a query window and run the following:

DECLARE @List AS VARCHAR(8)
    
SELECT @List = 'foobar'
    
SELECT * FROM dbo.PubLists WHERE Name = @List

How is this done in PostgreSQL? Can it be done?

Answer 1

You can declare local variables with :=, = in DECLARE clause as shown below:

DO $$
DECLARE
  value1 INT := 1; -- Here
  value2 INT = 2; -- Here
  value3 INT DEFAULT 3; -- Here
  value4 CONSTANT INT := 4; -- Here
  value5 INT; -- Here
BEGIN
  RAISE INFO '%', value1 + value2 + value3; -- 6
END
$$;

*Memos:

• :=, = and DEFAULT are the same.
• Trying to change the constant local variable value4 gets error.
• The uninitialized local variable value5 is NULL.
• You can declare local variables with DECLARE clause in a PL/pgSQL function and procedure and DO statement.

Answer 2

Given the popularity, and somewhat incomplete answers I'll provide two solutions.

1. A do block that won't return rows. You can return rows with a transaction cursor, but it's a bit messy.
2. A function (that returns rows)

Below I'll use an over-baked example of updating the tweet on the bottom right "blurb" with "hello world".

id (serial)	pub_id (text)	tweet (text)
1	abc	hello world
2	def	blurb

A simple do block

do $$
declare
    src_pub_id text;
    dst_pub_id text;
    src_id    int; 
    dest_id   int;
    src_tweet text;
begin
    src_pub_id := 'abc';
    dst_pub_id := 'def';
    
    -- query result into a temp variable
    src_id := (select id from tweets where pub_id = src_pub_id);

    -- query result into a temp variable (another way)
    select tweet into src_tweet from tweets where id = src_id;

    dest_id := (select id from tweets where pub_id = dst_pub_id);
    update tweets set tweet=src_tweet where id = dest_id;
end $$ language plpgsql; -- need the language to avoid ERROR 42P13

A function

create or replace function sync_tweets(
    src_pub_id text, -- function arguments
    dst_pub_id text
) returns setof tweets as -- i.e. rows. int, text work too
$$
declare
    src_id    int; -- temp function variables (not args)
    dest_id   int;
    src_tweet text;
begin
    -- query result into a temp variable
    src_id := (select id from tweets where pub_id = src_pub_id);

    -- query result into a temp variable (another way)
    select tweet into src_tweet from tweets where id = src_id;

    dest_id := (select id from tweets where pub_id = dst_pub_id);
    update tweets set tweet=src_tweet where id = dest_id;

    return query -- i.e. rows, return 0 with return int above works too
        select * from tweets where pub_id in (src_pub_id, dst_pub_id);
end
$$ language plpgsql; -- need the language to avoid ERROR 42P13

-- Run it!
select * from sync_tweets('abc', 'def');

-- Optional drop if you don't want the db to keep your function
drop function if exists sync_tweets(text, text);

/*
  Outputs
   __________________________________________________ 
  |  id (serial)  |  pub_id (text)  |  tweet (text)  |
  |---------------|-----------------|----------------|
  |  1            |  abc            |  hello world   |
  |  2            |  def            |  blurb         |
  --------------------------------------------------
*/

Answer 3

You can also simply make a constant query that you use in the actual query:

WITH vars as (SELECT 'foobar' AS list) 
SELECT *
FROM   dbo.PubLists, vars
WHERE  Name = vars.list

Answer 4

For use variables in for example alter table:

DO $$ 
DECLARE name_pk VARCHAR(200);
BEGIN
select constraint_name
from information_schema.table_constraints
where table_schema = 'schema_name'
      and table_name = 'table_name'
      and constraint_type = 'PRIMARY KEY' INTO name_pk;
IF (name_pk := '') THEN
EXECUTE 'ALTER TABLE schema_name.table_name DROP CONSTRAINT ' || name_pk;

Answer 5

Building on @nad2000's answer and @Pavel's answer here, this is where I ended up for my Flyway migration scripts. Handling for scenarios where the database schema was manually modified.

DO $$
BEGIN
    IF NOT EXISTS(
        SELECT TRUE FROM pg_attribute 
        WHERE attrelid = (
            SELECT c.oid
            FROM pg_class c
            JOIN pg_namespace n ON n.oid = c.relnamespace
            WHERE 
                n.nspname = CURRENT_SCHEMA() 
                AND c.relname = 'device_ip_lookups'
            )
        AND attname = 'active_date'
        AND NOT attisdropped
        AND attnum > 0
        )
    THEN
        RAISE NOTICE 'ADDING COLUMN';        
        ALTER TABLE device_ip_lookups
            ADD COLUMN active_date TIMESTAMP;
    ELSE
        RAISE NOTICE 'SKIPPING, COLUMN ALREADY EXISTS';
    END IF;
END $$;

Answer 6

Postgresql does not have bare variables, you could use a temporary table. variables are only available in code blocks or as a user-interface feature.

If you need a bare variable you could use a temporary table:

CREATE TEMP TABLE list AS VALUES ('foobar');

SELECT dbo.PubLists.*
FROM   dbo.PubLists,list
WHERE  Name = list.column1;

Answer 7

I had to do something like this

CREATE OR REPLACE FUNCTION MYFUNC()
RETURNS VOID AS $$
DO
$do$
BEGIN
DECLARE
 myvar int;
 ...
END
$do$
$$ LANGUAGE SQL;

Answer 8

DO $$
DECLARE  
   a integer := 10;  
   b integer := 20;  
   c integer;  
BEGIN  
   c := a + b;
    RAISE NOTICE'Value of c: %', c;
END $$;

Answer 9

I've came across some other documents which they use \set to declare scripting variable but the value is seems to be like constant value and I'm finding for way that can be acts like a variable not a constant variable.

Ex:

\set Comm 150

select sal, sal+:Comm from emp

Here sal is the value that is present in the table 'emp' and comm is the constant value.

Answer 10

You can use:

\set list '''foobar'''
SELECT * FROM dbo.PubLists WHERE name = :list;

That will do

Answer 11

Complete answer is located in the official PostgreSQL documentation.

You can use new PG9.0 anonymous code block feature (http://www.postgresql.org/docs/9.1/static/sql-do.html )

DO $$
DECLARE v_List TEXT;
BEGIN
  v_List := 'foobar' ;
  SELECT *
  FROM   dbo.PubLists
  WHERE  Name = v_List;
  -- ...
END $$;

Also you can get the last insert id:

DO $$
DECLARE lastid bigint;
BEGIN
  INSERT INTO test (name) VALUES ('Test Name') 
  RETURNING id INTO lastid;

  SELECT * FROM test WHERE id = lastid;
END $$;

Answer 12

Here's an example of using a variable in plpgsql:

create table test (id int);
insert into test values (1);
insert into test values (2);
insert into test values (3);

create function test_fn() returns int as $$
    declare val int := 2;
    begin
        return (SELECT id FROM test WHERE id = val);
    end;
$$ LANGUAGE plpgsql;

SELECT * FROM test_fn();
 test_fn 
---------
       2

Have a look at the plpgsql docs for more information.