Take the 2-minute tour ×

I'm migrating from SQL Server to PostgreSQL. I've seen from http://stackoverflow.com/questions/1490942/how-to-declare-a-variable-in-a-postgresql-query that there is no such thing as temporary variables in native sql queries.

Well, I pretty badly need a few... How would I go about mixing in plpgsql? Must I create a function and then delete the function in order to get access to a language? that just seems error prone to me and I'm afraid I'm missing something.


cmd.CommandText="insert......" +
"declare @app int; declare @gid int;"+
"set @app=SCOPE_IDENTITY();"+ //select scope_identity will give us our RID that we just inserted
"select @gid=MAX(GROUPID) from HOUSEHOLD; set @gid=@gid+1; "+
"select @app";

A direct rip from the application in which it's used. Note we are in the process of converting from SQL server to Postgre. (also, I've figured out the scope_identity() bit I think)

share|improve this question
Did you mean temporary table? –  Andrew Hare Dec 21 '09 at 16:48
no, variables. Like declare @foo int; –  Earlz Dec 21 '09 at 16:50
Ah, you mean table variable then (odetocode.com/articles/365.aspx) –  Andrew Hare Dec 21 '09 at 16:51
not really, plus thats not postgresql –  Earlz Dec 21 '09 at 16:53
can you show a piece of code that you think requires the use of variables? postgresql is different from sql server, maybe your usage pattern doesn't translate as literally as you hoped. –  just somebody Dec 21 '09 at 17:40

8 Answers 8

up vote 3 down vote accepted

What is your schema for the table being inserted? I'll try and answer based on this assumption of the schema:

    APPLICANT_RID SERIAL,  -- PostgreSQL auto-increment

If I'm understanding your intent correctly, in PostgreSQL >= 8.2, the query would then be:


-- Added call to the COALESCE function to cover the case where HOUSEHOLD 
-- is empty and MAX(GROUPID) returns NULL

In PostgreSQL >= 8.2, any INSERT/DELETE/UPDATE query may have a RETURNING clause that acts like a simple SELECT performed on the result set of the change query.

share|improve this answer
I was not aware you could nest selects inside of inserts and such.. I guess I just need to read up more on Ansi SQL(if that is standard anyway) –  Earlz Dec 22 '09 at 18:10

If you're using a language binding, you can hold the variables there.

For example with SQLAlchemy (python):

my_var = 'Reynardine'

If you're in psql, you have variables:

\set a 5

And if your logic is in PL/pgSQL:

tax := subtotal * 0.06;
share|improve this answer
I am wanting to do these queries from my own program, not from psql –  Earlz Dec 21 '09 at 17:13
Thanks. What language binding are you using? –  Tobu Dec 21 '09 at 17:14
plpgsql.. I'm not sure how to use it freestanding though... (like in a sql query without creating a function) –  Earlz Dec 21 '09 at 17:18
I was thinking of a library like SQLAlchemy, psycopg2 or ActiveRecord. –  Tobu Dec 21 '09 at 17:33
@earlz: You cannot. Procedural languages are for functions/procedures only. –  Alex Brasetvik Dec 21 '09 at 17:55


Yes, but this shortcoming is going to be removed in PostgreSQL 8.5, with the addition of DO command. 8.5 is going to be released in 2010.

share|improve this answer

You can also declare session variables using plperl - http://www.postgresql.org/docs/8.4/static/plperl-global.html

share|improve this answer

I think that PostgreSQL's row-type variable would be the closest thing:


share|improve this answer

you install a language that you want to use with the CREATE LANGUAGE command for known languages. Although you can use other languages.

Language installation docs


You will have to create a function to use it. If you do not want to make a permanent function in the db then the other choice would be to use a scrip in python or something that uses a postgresql driver to connect to the db and do queries. You can then manipulate or look through the data in the script. For instance in python you would install the pygresql library and in your script import pgdb which you can use to connect to the db.

PyGreSQL Info

share|improve this answer

You mentioned the post (How to declare a variable in a PostgreSQL query).

I believe there is a suitable answer farther down the chain of solutions if using psql and the \set command:

my_db=> \set myvar 5
my_db=> SELECT :myvar  + 1 AS my_var_plus_1;
share|improve this answer
This seems more like a parameter binding than a session variable. –  Nick Hristov May 15 at 15:05

Your Answer


