Background: I'm writing my first pgTAP test cases for a PL/pgSQL function and starting small with psql test scripts. No problems on that but I run into a small annoyance on psql variables.
In my test scripts I first dump quite a bit of test data to a relevant tables and later refer to the data with a primary keys that were generated by sequences. I'd find it handy to be able to create a variable that would contain the primary key. This is what I'm looking for:
scalasb=> \set the_id (select currval('id_data_id_seq'))
scalasb=> \echo :the_id
54754
scalasb=>
But this is what I get:
scalasb=> \set the_id (select currval('id_data_id_seq'))
scalasb=> \echo :the_id
(selectcurrval('id_data_id_seq'))
scalasb=>
I have a workaround (please see the example below), but it looks like psql variables are not the right tool for this job. Or they are just a different of what I've used with Oracle sqlplus bind variables ...
So my question: how I can bind the return value of a SQL query into a variable in psql script ?
I'm working on a linux with 9.1.
-- this is a simplified example to illustrate the problem
begin;
create table id_data(id serial primary key, data text not null);
create or replace function get_text(p_id bigint)
returns text
as $$
declare
v_data constant text := data from id_data where id = p_id;
begin
return v_data;
end;
$$ language plpgsql;
insert into id_data(data) values('lorem ipsum');
-- this works correctly but is a rather verbose (especially when one have
-- more of these, in the same query/function)
select get_text((select currval('id_data_id_seq')));
-- instead I'd like to set the id to a variable and use that but this
-- seems to be impossible with psql, right ?
\set the_id (select currval('id_data_id_seq'))
\echo First try: :the_id
--select get_text(:the_id); -- this will fail
-- this works and reveals psql variables' true nature - they are just a
-- textual replacements
\set the_id '(select currval(\'id_data_id_seq\'))'
\echo Second try: :the_id
select get_text(:the_id);
rollback;
