Thread: defining a variable

Hi All.

I need to create a sql script and launch it from pgadmin. In this script
some sql statements (INSERT) have to depend from the result of previous
statements (SELECT).
Is there a way to define a variable in SQL and set its value with the result
of a SELECT query?

Using the psql console I've used the command set var 'val'; and this
statement works fine.
Usising the same command from pgadmin an error is reported.

Im using postgres 8.2 on WindowsXp environment.

Thanks in advance.

Luca.
 --
 Email.it, the professional e-mail, gratis per te: http://www.email.it/f

 Sponsor:
 VOGLIA DI VACANZE ?
A Riccione i Family Hotels sono gli alberghi specializzati per le vacanze
dei bambini
 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=7983&d=20080612

On 12/06/2008 14:37, luca.ciciriello@email.it wrote:

> I need to create a sql script and launch it from pgadmin. In this script
> some sql statements (INSERT) have to depend from the result of previous
> statements (SELECT).
> Is there a way to define a variable in SQL and set its value with the result
> of a SELECT query?

Depending on what you're doing, you could use INSERT INTO....SELECT....
like this:

test=# create table t2(f1 integer, f2 integer);
CREATE TABLE
test=# create table t2(f1 integer, f2 integer);
CREATE TABLE
test=# insert into t1 values (1,2);
INSERT 0 1
test=# insert into t1 values (3,4);
INSERT 0 1
test=# select * from t1;
  f1 | f2
----+----
   1 |  2
   3 |  4
(2 rows)

test=# insert into t2(f1, f2) select f1, f2 from t1;
INSERT 0 2
test=# select * from t2;
  f1 | f2
----+----
   1 |  2
   3 |  4
(2 rows)

Or you could use a pl/pgsql function, which lets you declare as many
variables as you like.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------