How do you use variables in a simple PostgreSQL sc

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 use:

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

That will do

Answer 2

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 3

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 4

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 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

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;