"parameter name used more than once" error when declaring function - why this is happening?

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)

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.

StackExchange Database Administrators Q#115665, answer score: 6