postgresqlecpg

PostgreSQL ecpg: How to call function with several out parameters

Suppose I have stored function foobar:

create or replace function foobar(
  out p_foo varchar,
  out p_bar varchar
)
returns record as $func$
begin
  p_foo := 'foo';
  p_bar := 'bar';
end;
$func$ language plpgsql;

What ist the idiomatic way to call this function from an ecpg program? The best I have found so far is

EXEC SQL SELECT (foobar()).p_foo, (foobar()).p_bar into :foo,:bar;

or

EXEC SQL SELECT (y).p_foo, (y).p_bar into :foo,:bar from (select foobar() y) x;

but this seems rather clumsy.

Solution 
  • EXEC SQL SELECT p_foo, p_bar INTO :foo, :bar FROM foobar();