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

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

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

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

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

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

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

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

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

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

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