CODEWITHSUNDEEP
sql-serverpostgresqlvariablescompatibility
Earlz
Earlz

Reputation: 63895

Declaring temporary variables in PostgreSQL

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)

Upvotes: 0

Views: 13787

Answers (7)

leroyJr
leroyJr

Reputation: 1160

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;

Upvotes: -1

Matthew Wood
Matthew Wood

Reputation: 16427

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.

Upvotes: 3

filiprem
filiprem

Reputation: 276

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.

Upvotes: 1

Arthur Thomas
Arthur Thomas

Reputation: 5187

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

Upvotes: 0

Tobu
Tobu

Reputation: 25436

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;

Upvotes: 1

Andrew Hare
Andrew Hare

Reputation: 351616

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.

Upvotes: -1

Alex Brasetvik
Alex Brasetvik

Reputation: 11744

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

Upvotes: 0

Related Questions