Re: setting and using variables in PSQL ????

On Dec 14, 2007, at 9:19 PM, Gauthier, Dave wrote:

> At the PSQL prompt, I want to set some variables based upon query
> results, or via static assignment, then insert a record with those
> values.  Sort of like...
>
> select val1 into x from agedata where name = ‘joe_mako’;
> select val12 into y from sizedata where name = ‘joe_mako’;
> thename := ‘joe_mako’;
> insert into newtable (name, age, height) values (thename,x,y);
>
> Some sort of compound insert statement would solve my problem, but
> in a larger sense, how do you (can you ) do something like this
> inside a statement blocks of some kind?  I’ve done some PL-pgsql and
> know about statement blocks in that context.  But I don’t want to
> have to create a function, use it, then delete it.  Was wondering of
> something else could be done.

Do it all from inside the database...

granicus% cat ttab.sql
create temp table agedata (name text, val1 integer);
insert into agedata values ('joe_mako', 30);
create temp table sizedata (name text, val12 integer);
insert into sizedata values ('joe_mako', 200);

create temp table newtable as select
         'joe_mako'::text as name,
         (select val1  from agedata where name = 'joe_mako') as age,
         (select val12 from sizedata where name = 'joe_mako') as size
;

select * from newtable;
granicus% psql -f ttab.sql
CREATE TABLE
INSERT 0 1
CREATE TABLE
INSERT 0 1
SELECT
    name   | age | size
----------+-----+------
  joe_mako |  30 |  200
(1 row)

granicus%

but I probably wouldn't do it this way at all.  something like
(untested)
select A.name, A.val1 as age, S.val12 as size
    from agedata A left outer join sizedata S on S.name = A.name;

should work.

--
Nathan Wagner
nw@hydaspes.if.org