How do you use variables in a simple PostgreSQL script?

Admin 1
Viewed 344682

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?

postgresql variables
10 Answers

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.

user_696890

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 $$;
user_11588440

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

How do I do this in pgamdin?

  DECLARE SET varchar(20) @TABELA = '' ; 
DECLARE SET varchar(20) @SCHM = '' ; 
DECLARE SET VARCHAR(255) @MSG =''; 
SELECT
    'CREATE TABLE '
    || TRIM(TABLE_OWNER)
    || '.'
    || 'BKPCTRL'
    || SUBSTRING(CAST(CURRENT_DATE AS VARCHAR(10)),1,4)
    || SUBSTRING(CAST(CURRENT_DATE AS VARCHAR(10)),6,2)
    || SUBSTRING(CAST(CURRENT_DATE AS VARCHAR(10)),9,2)
    || SUBSTRING(CAST(CURRENT_TIME AS VARCHAR(12)),1,2)
    || SUBSTRING(CAST(CURRENT_TIME AS VARCHAR(12)),4,2)
    || '_'
    || TRIM(TABLE_NAME)
    || ' AS SELECT * FROM '
    || @MSG
    || TRIM(TABLE_OWNER)
    || '.'
    || TRIM(TABLE_NAME)
FROM SYSTABLE
WHERE 
    TABLE_NAME = @TABELA
    AND TABLE_OWNER = @SCHM
    AND TABLE_TYPE = 'TABLE'
    
    
DECLARE @TABELA integer = ''
select
'CREATE TABLE "'||TABELA||'" AS SELECT * FROM "'||TABELA||'" ;' 
from SYSTABLE 
WHERE  TABLE = @TABELA
user_200728650
Related