4
create function test(a integer, b integer)
returns table (a integer, b integer) as $$
  begin
    return query select 1, 2;
  end;
$$ language plpgsql;

throws exception:

ERROR:  parameter name "a" used more than once
CONTEXT:  compilation of PL/pgSQL function "test" near line 1

Why this is happening? I thought that function argument name has nothing to do with returned table column name. Is there a workaround to have function argument named "a" and also have column named "a" in a table returned from function?

I know that I can return setof record but then I have to explicitly "define" columns returned from function in select query:

create function test(a integer, b integer)
returns setof record as $$
  begin
    return query select 1, 2;
  end;
$$ language plpgsql;

select * from test(1, 2) as (a integer, b integer)
5

You've defined an input variable and a return variable with the same name, so it can't be disambiguated by PL/PGSQL, as the error message is telling you.

You can change it to returns table (c integer, d integer) as well, or you can create a type for this, like CREATE TYPE mytype (a integer, b integer) and then use it in your function as returns setof mytype, which should give you the behavior you're looking for, without colliding variables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.