Thread: 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: > 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: > 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 createa 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 > 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 > > > > > >