Re: Out parameters handling

Hello

2009/3/7 Rod Taylor <rod(dot)taylor(at)gmail(dot)com>:
>> actually - function name should be used as label now. This code is working:
>
> Not helpful for me. The most typical conflict I have is actually the
> OUT parameter and table name, not a column of the table.
>

This conflict I never meet. And I afraid so this should not be solved.
One typical beginer's bug has similar symptoms.

create function foo(tablename varchar, param varchar, paramname
varchar) returns ..
begin
select into .. .. from tablename where .paramname = param
....

This is bug - who can understand, if this is desired behave or nonsense.

you have to use dynamic SQL. All what are inside literal, are independent.

postgres=# create table wrong(a integer);
CREATE TABLE
postgres=# insert into wrong values(10);
INSERT 0 1
postgres=# create function fx3(out wrong varchar) returns setof varchar as $$
begin
for wrong in execute 'select * from wrong'
loop
return next;
end loop;
return; end; $$ language plpgsql;
CREATE FUNCTION
postgres=# select * from fx3();
┌───────┐
│ wrong │
├───────┤
│ 10 │
└───────┘
(1 row)

regards
Pavel Stehule

Actually dynamic sql are little bit uncomfortable. It's much better in 8.4.

regards
Pavel Stehule

> Really don't want to prefix all tables with a hardcoded schema or do
> variable substitution for loading the document.
>
> Not fond of prefixing with function name either as a) many of my
> functions have very long names and b) they change names occasionally,
> particularly during development.
>
> A short prefix like "out" would be useful. I would immediately start
> prefixing all uses.
>
> rbt=# begin;
> BEGIN
> rbt=# create table b (col integer);
> CREATE TABLE
> rbt=# insert into b values (2);
> INSERT 0 1
> rbt=# create or replace function fx2(a integer, out b integer) as $$
> rbt$# begin
> rbt$#   SELECT col
> rbt$#     INTO fx2.b
> rbt$#     FROM b;
> rbt$#
> rbt$#   return;
> rbt$# end; $$ language plpgsql;
> ERROR:  syntax error at or near "$1"
> LINE 1: SELECT col FROM  $1
>                         ^
> QUERY:  SELECT col FROM  $1
> CONTEXT:  SQL statement in PL/PgSQL function "fx2" near line 4
> rbt=#
>