Variables in Postgresql
Hi.
I haven't seen any documentation about this, how do you use variables in a
simple way in postgresql? I want to use same sequence number in a dynamic
query from Perl.
When i'm using MSSQL i can do like this in a single query
"DECLARE @owner INT
INSERT maintable (fields) VALUES (...)
SELECT @owner = @@IDENTITY
INSERT subtable (fields) VALUES (@owner, ...)"
How does I save a value and using it later in the query..?
kind regards
Tomas
Assuming you have table foo with a sequence foo_seq, you can do this:
insert into foo (fields) values (....)
insert into bar (fields) select foo_seq.currval, ....
In other words, foo_seq.currval will always return the last value used by
your transaction.
-alex
On Mon, 4 Jun 2001, Tomas Eriksson wrote:
Show quoted text
Hi.
I haven't seen any documentation about this, how do you use variables in a
simple way in postgresql? I want to use same sequence number in a dynamic
query from Perl.When i'm using MSSQL i can do like this in a single query
"DECLARE @owner INT
INSERT maintable (fields) VALUES (...)
SELECT @owner = @@IDENTITY
INSERT subtable (fields) VALUES (@owner, ...)"How does I save a value and using it later in the query..?
kind regards
Tomas---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Thanks for you answer. In that case it works fine with the sequence function. Just to handle simple values between select-statements, variables is very useful. What I have seen this is only possible in pl/pgsql and I don't want to create a function of everything.
/Tomas
----- Original Message -----
From: "Alex Pilosov" <alex@pilosoft.com>
To: "Tomas Eriksson" <tomas@embryo.se>
Cc: <pgsql-general@postgresql.org>
Sent: Monday, June 11, 2001 3:31 PM
Subject: Re: [GENERAL] Variables in Postgresql
Show quoted text
Assuming you have table foo with a sequence foo_seq, you can do this:
insert into foo (fields) values (....)
insert into bar (fields) select foo_seq.currval, ....In other words, foo_seq.currval will always return the last value used by
your transaction.-alex
On Mon, 4 Jun 2001, Tomas Eriksson wrote:Hi.
I haven't seen any documentation about this, how do you use variables in a
simple way in postgresql? I want to use same sequence number in a dynamic
query from Perl.When i'm using MSSQL i can do like this in a single query
"DECLARE @owner INT
INSERT maintable (fields) VALUES (...)
SELECT @owner = @@IDENTITY
INSERT subtable (fields) VALUES (@owner, ...)"How does I save a value and using it later in the query..?
kind regards
Tomas---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
You could use a temporary table.
Jon
johnnyb6@sdf.lonestar.org
SDF Public Access UNIX System - http://sdf.lonestar.org
On Mon, 4 Jun 2001, Tomas Eriksson wrote:
Show quoted text
Hi.
I haven't seen any documentation about this, how do you use variables in a
simple way in postgresql? I want to use same sequence number in a dynamic
query from Perl.When i'm using MSSQL i can do like this in a single query
"DECLARE @owner INT
INSERT maintable (fields) VALUES (...)
SELECT @owner = @@IDENTITY
INSERT subtable (fields) VALUES (@owner, ...)"How does I save a value and using it later in the query..?
kind regards
Tomas---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly