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@xxxxxxxxxxxxxxx
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
