Declaring temporary variables in PostgreSQL

Question

I'm migrating from SQL Server to PostgreSQL. I've seen from 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.

EDIT:

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; "+
"insert into HOUSEHOLD (APPLICANT_RID,GROUPID,ISHOH) values "+
"(@app,@gid,1);"+
"select @app";
rid=cmd.ExecuteScalar();

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)

Answer 1

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

CREATE TABLE HOUSEHOLD (
    APPLICANT_RID SERIAL,  -- PostgreSQL auto-increment
    GROUPID INTEGER,
    ISHOH INTEGER
);

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

INSERT INTO HOUSEHOLD (GROUPID, ISHOH)
VALUES ((SELECT COALESCE(MAX(GROUPID)+1,1) FROM HOUSEHOLD), 1)
RETURNING APPLICANT_RID;

-- 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.

Answer 2

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

For example with SQLAlchemy (python):

my_var = 'Reynardine'
session.query(User.name).filter(User.fullname==my_var)

If you're in psql, you have variables:

\set a 5
SELECT :a;

And if your logic is in PL/pgSQL:

tax := subtotal * 0.06;

Answer 3

Must I create a function and then delete the function in order to get access to a language?

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.

Answer 4

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

Answer 5

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

CREATE LANGUAGE usage doc

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

Answer 6

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

A variable of a composite type is called a row variable (or row-type variable). Such a variable can hold a whole row of a SELECT or FOR query result, so long as that query's column set matches the declared type of the variable.

Answer 7

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;