How do you use variables in a simple PostgreSQL script?

by

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

More Related Contents:

  1. Can I make a plpgsql function return an integer without using a variable?
  2. User defined variables in PostgreSQL
  3. Loop on tables with PL/pgSQL in Postgres 9.0+
  4. Why the “Group BY” by month didn’t work? [closed]
  5. What to do with null values when modeling and normalizing?
  6. Postgres: INSERT if does not exist already
  7. Truncating all tables in a Postgres database
  8. How can I drop all the tables in a PostgreSQL database?
  9. How to change PostgreSQL user password?
  10. How to get the count of current month Sunday’s in psql?
  11. Permission denied for relation
  12. Execute a dynamic crosstab query
  13. Can I automatically create a table in PostgreSQL from a csv file with headers?
  14. PostgreSQL query very slow with limit 1
  15. How to add an auto-incrementing primary key to an existing table, in PostgreSQL?
  16. Postgres query optimization (forcing an index scan)
  17. psql: FATAL: database “” does not exist
  18. Difference between timestamps with/without time zone in PostgreSQL
  19. Omitting the double quote to do query on PostgreSQL
  20. Storing Images in PostgreSQL
  21. Psql could not connect to server: No such file or directory, 5432 error?
  22. In PostgreSQL, how to insert data with COPY command?
  23. How to add column if not exists on PostgreSQL?
  24. Maximum characters in labels (table names, columns etc)
  25. Docker – Postgres and pgAdmin 4 : Connection refused
  26. How to work with PGpoint for Geolocation using PostgreSQL?
  27. Postgres default sort by id – worldship
  28. Best way to install hstore on multiple schemas in a Postgres database?
  29. Loop through columns of RECORD
  30. How to drop PostgreSQL database through command line [closed]

Leave a Comment