Thread: 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°