How do you use script variables in psql

Question

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 the particular situation  Since it s at the top of the script it s easy to see and remember   How do I do the same with the PostgreSQL client psql

User · Answer

Variables in psql suck  If you want to declare an integer  you have to enter the integer  then do a carriage return  then end the statement in a semicolon  Observe   Let s say I want to declare an integer variable my var and insert it into a table test   Example table test   thedatabase    d test                           Table  public test   Column    Type                         Modifiers                      -------- --------- ---------------------------------------------------  id       integer   not null default nextval  test id seq   regclass  Indexes       test pkey  PRIMARY KEY  btree  id    Clearly  nothing in this table yet   thedatabase   select   from test   id  ----  0 rows    We declare a variable  Notice how the semicolon is on the next line   thedatabase    set my var 999 thedatabase       Now we can insert  We have to use this weird       looking syntax   thedatabase   insert into test id  values    my var    INSERT 0 1   It worked   thedatabase   select   from test   id   -----  999  1 row    Explanation   So    what happens if we don t have the semicolon on the next line  The variable  Have a look   We declare my var without the new line   thedatabase    set my var 999    Let s select my var   thedatabase   select   my var     column   ----------  999   1 row    WTF is that  It s not an integer  it s a string 999    thedatabase   select 999    column   ----------       999  1 row

User · Answer

postgres  since version 9 0  allows anonymous blocks in any of the supported server-side scripting languages  DO   DECLARE somevariable int   -1  BEGIN INSERT INTO foo VALUES   somevariable    END       http   www postgresql org docs current static sql-do html  As everything is inside a string  external string variables being substituted in will need to be escaped and quoted twice   Using dollar quoting instead will not give full protection against SQL injection

User · Answer

You can try to use a WITH clause   WITH vars AS  SELECT 42 AS answer  3 14 AS appr pi  SELECT t    vars answer  t radius vars appr pi FROM table AS t  vars

User · Answer

I ve found this question and the answers extremely useful  but also confusing   I had lots of trouble getting quoted variables to work  so here is the way I got it working    set deployment user username    -- username  set deployment pass    string password    ALTER USER  deployment user WITH PASSWORD  deployment pass    This way you can define the variable in one statement   When you use it  single quotes will be embedded into the variable   NOTE   When I put a comment after the quoted variable it got sucked in as part of the variable when I tried some of the methods in other answers   That was really screwing me up for a while   With this method comments appear to be treated as you d expect

User · Answer

One final word on PSQL variables    They don t expand if you enclose them in single quotes in the SQL statement   Thus this doesn t work   SELECT   FROM foo WHERE bar     myvariable   To expand to a string literal in a SQL statement  you have to include the quotes in the variable set  However  the variable value already has to be enclosed in quotes  which means that you need a second set of quotes  and the inner set has to be escaped  Thus you need    set myvariable    somestring      SELECT   FROM foo WHERE bar    myvariable   EDIT  starting with PostgreSQL 9 1  you may write instead    set myvariable somestring SELECT   FROM foo WHERE bar     myvariable

User · Answer

I ve posted a new solution for this on another thread   It uses a table to store variables  and can be updated at any time  A static immutable getter function is dynamically created  by another function   triggered by update to your table   You get nice table storage  plus the blazing fast speeds of an immutable getter

User · Answer

FWIW  the real problem was that I had included a semicolon at the end of my  set command       set owner password  thepassword     The semicolon was interpreted as an actual character in the variable       echo  owner password   thepassword    So when I tried to use it      CREATE ROLE myrole LOGIN UNENCRYPTED PASSWORD  owner password NOINHERIT CREATEDB CREATEROLE VALID UNTIL  infinity        I got this      CREATE ROLE myrole LOGIN UNENCRYPTED PASSWORD thepassword  NOINHERIT CREATEDB CREATEROLE VALID UNTIL  infinity     That not only failed to set the quotes around the literal  but split the command into 2 parts  the second of which was invalid as it started with  NOINHERIT      The moral of this story  PostgreSQL  variables  are really macros used in text expansion  not true values  I m sure that comes in handy  but it s tricky at first

User · Answer

Another approach is to  ab use the PostgreSQL GUC mechanism to create variables  See this prior answer for details and examples   You declare the GUC in postgresql conf  then change its value at runtime with SET commands and get its value with current setting        I don t recommend this for general use  but it could be useful in narrow cases like the one mentioned in the linked question  where the poster wanted a way to provide the application-level username to triggers and functions

User · Answer

I solved it with a temp table    CREATE TEMP TABLE temp session variables        sessionSalt  TEXT    INSERT INTO temp session variables   sessionSalt   VALUES  current timestamp    RANDOM    TEXT     This way  I had a  variable  I could use over multiple queries  that is unique for the session  I needed it to generate unique  usernames  while still not having collisions if importing users with the same user name

User · Answer

Specifically for psql  you can pass psql variables from the command line too  you can pass them with -v  Here s a usage example     psql -v filepath  path to my directory mydatafile data regress regress  gt  SELECT   filepath                   column                  ---------------------------------------   path to my directory mydatafile data  1 row    Note that the colon is unquoted  then the variable name its self is quoted  Odd syntax  I know  This only works in psql  it won t work in  say  PgAdmin-III   This substitution happens during input processing in psql  so you can t  say  define a function that uses   filepath  and expect the value of   filepath  to change from session to session  It ll be substituted once  when the function is defined  and then will be a constant after that  It s useful for scripting but not runtime use

User · Answer

You need to use one of the procedural languages such as PL pgSQL not the SQL proc language  In PL pgSQL you can use vars right in SQL statements  For single quotes you can use the quote literal function

User · Answer

Postgres variables are created through the  set command  for example       set myvariable value       and can then be substituted  for example  as      SELECT   FROM  myvariable table1        or      SELECT   FROM table1 WHERE  myvariable IS NULL    edit  As of psql 9 1  variables can be expanded in quotes as in      set myvariable value   SELECT   FROM table1 WHERE column1     myvariable     In older versions of the psql client         If you want to use the variable as the value in a conditional string query  such as      SELECT   FROM table1 WHERE column1     myvariable         then you need to include the quotes in the variable itself as the above will not work   Instead define your variable as such       set myvariable  value    However  if  like me  you ran into a situation in which you wanted to make a string from an existing variable  I found the trick to be this       set quoted myvariable       myvariable        Now you have both a quoted and unquoted variable of the same string   And you can do something like this       INSERT INTO  myvariable table1 SELECT   FROM table2 WHERE column1    quoted myvariable

User · Answer

I really miss that feature  Only way to achieve something similar is to use functions   I have used it in two ways    perl functions that use   SHARED variable store your variables in table   Perl version      CREATE FUNCTION var name text  val text  RETURNS void AS              SHARED    0        1         LANGUAGE plperl     CREATE FUNCTION var name text  RETURNS text AS            return   SHARED    0          LANGUAGE plperl    Table version   CREATE TABLE var     sess bigint NOT NULL    key varchar NOT NULL    val varchar    CONSTRAINT var pkey PRIMARY KEY  sess  key     CREATE FUNCTION var key varchar  val anyelement  RETURNS void AS      DELETE FROM var WHERE sess   pg backend pid   AND key    1    INSERT INTO var  sess  key  val  VALUES  sessid     1   2  varchar      LANGUAGE  sql    CREATE FUNCTION var varname varchar  RETURNS varchar AS      SELECT val FROM var WHERE sess   pg backend pid   AND key    1     LANGUAGE  sql     Notes    plperlu is faster than perl pg backend pid is not best session identification  consider using pid combined with backend start from pg stat activity this table version is also bad because you have to clear this is up occasionally  and not delete currently working session variables

[sql] How do you use script variables in psql?

Examples related to sql

Examples related to postgresql

Examples related to variables

Examples related to psql