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

User · Answer

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

User · Answer

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  gt  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 · Answer

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

DO    DECLARE      a integer    10       b integer    20       c integer    BEGIN      c    a   b      RAISE NOTICE Value of c      c  END

User · Answer

You can use    set list    foobar    SELECT   FROM dbo PubLists WHERE name    list    That will do

User · Answer

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

User · Answer

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

User · Answer

For the official CLI client  psql  see here  And  pgAdmin3  1 10  still in beta  has  pgScript

User · Answer

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

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

[postgresql] How do you use variables in a simple PostgreSQL script?

Examples related to postgresql

Examples related to variables