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

Gauthier, Dave <dave.gauthier@intel.com> schrieb:

Hi:

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...

You can use this:

- define in your postgresql.conf:
custom_variable_classes = 'myvar'

- use within psql:

test=# set myvar.benutzer = 'foo';
SET
test=*# select * from foo;
id | name
----+------
1 | foo
2 | bar
(2 rows)

test=*# select * from foo where name=current_setting('myvar.benutzer');
id | name
----+------
1 | foo
(1 row)

test=*# set myvar.benutzer = 'none';
SET
test=*# select * from foo where name=current_setting('myvar.benutzer');
id | name
----+------
(0 rows)

HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�