Postgresql Stored Procedures: Using id from one statement in another

Question

Is it possible to create a stored procedure with two insert statements where id/primary_key from the first insert statement will be used in second.

For eg.

INSERT INTO activity VALUES (DEFAULT, 'text', 'this is a test');

If the id returned from the above statement is x, the second insert will be like:

INSERT INTO activity_tree VALUES (DEFAULT, **x**, user_id) or something like that.

I understand thatlibpq has functions which can give the id from the first statement.

But I want to combine them into a stored procedure. Please advise.

Regards, Mayank

user330315 · Accepted Answer · 2011-05-02 17:20:08Z

5

Declare a variable, e.g. new_id and then you can store the generate id in there:

INSERT INTO activity VALUES (DEFAULT, 'text', 'this is a test')
   RETURNING id INTO new_id;

INSERT INTO activity_tree VALUES (DEFAULT, new_id, user_id);

This assumes the column where the value is generated is called id

Btw: do not use "unqualified" insert statements. Always specify the columns in the INSERT part. That makes your code much more stable:

INSERT INTO activity (id, some_column, other_column) 
VALUES 
(DEFAULT, 'text', 'this is a test')

And make sure you are not calling variables the same way as columns. user_id seems to be a potential naming conflict here. This will compile with 8.x but might give you strange errors. I think this will no longer compile with 9.x

answered May 2, 2011 at 17:20

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Mayank Over a year ago

Excellent Info. Thanks a lot. I'll take care of each and everything you suggested. Thanks once again

Mayank Over a year ago

Accepted. I did not know about this provision. I'm relatively new to Stackoverflow :)

Collectives™ on Stack Overflow

Postgresql Stored Procedures: Using id from one statement in another

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related