Re: defining a variable

On 12/06/2008 14:37, luca(dot)ciciriello(at)email(dot)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(at)iol(dot)ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------