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