How do you use variables in a simple PostgreSQL script?

postgresql variables
ChristineDev picture ChristineDev · Apr 20, 2009 · Viewed 263.5k times · Source

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?

Answer

nad2000 picture nad2000 · Aug 9, 2011

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

Related questions

How do you use script variables in psql?

In MS SQL Server, I create my scripts to use customizable variables: DECLARE @somevariable int SELECT @somevariable = -1 INSERT INTO foo VALUES ( @somevariable ) I'll then change the value of @somevariable at runtime, depending on the value that I want in …

Read More
Loop on tables with PL/pgSQL in Postgres 9.0+

I want to loop through all my tables to count rows in each of them. The following query gets me an error: DO $$ DECLARE tables CURSOR FOR SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg_%' ORDER BY …

Read More
Declare variable set = select

This probably sounds like a really stupid question, but how do I declare a variable for used in a PostgreSQL 9.3 query? CREATE or replace FUNCTION public.test() returns int4 AS $BODY$ DECLARE cod_process bigint :=30001; cod_instance bigint ; utc_log …

Read More