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?
This question is related to
postgresql
variables
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;
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;
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 $$;
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.
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.
DO $$
DECLARE
a integer := 10;
b integer := 20;
c integer;
BEGIN
c := a + b;
RAISE NOTICE'Value of c: %', c;
END $$;
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;
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 $$;
You can use:
\set list '''foobar'''
SELECT * FROM dbo.PubLists WHERE name = :list;
That will do
Source: Stackoverflow.com