How do you use variables in a simple PostgreSQL script?

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

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

You can use:

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

That will do

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.

How do you use variables in a simple PostgreSQL script?

Related

Recent Posts