From: Richard Hayward <richard@tortoise.demon.co.uk>
Subject: function parameters : bug?
Date: Sun, 10 Jul 2005 11:52:10 +0100
Organization: Hub.Org Networking Services
Lines: 52
Message-ID: <ugu1d19614tuteki2jgm0496d58kcggdhn@4ax.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
To: pgsql-sql@postgresql.org

pg 8.0.3
This behaviour seems odd to me:

CREATE TABLE mytable (
  inta INTEGER
);

CREATE OR REPLACE FUNCTION myfunction (inta integer) RETURNS integer 
AS 
$body$ 
begin
  insert into mytable(inta) values (inta);
  return(0);
end;
$body$
LANGUAGE 'plpgsql';

select myfunction(123);

ERROR:  syntax error at or near "$1" at character 22
QUERY:  insert into myTable( $1 ) values ( $2 )
CONTEXT:  PL/pgSQL function "myfunction" line 2 at SQL statement

The problem is with the line in my myfunction:
  insert into mytable(inta) values (inta);

The server doesn't like the field name being the same as the name of
the function parameter. I don't see why, as I can see no opportunity
for ambiguity.

Problem vanishes with a different function parameter name:

CREATE OR REPLACE FUNCTION myfunction (my_inta integer) RETURNS
integer 
AS $body$ begin
  insert into myTable(inta) values (my_inta);
  return(0);
end;
$body$
LANGUAGE 'plpgsql'


But, this means that if I change the design of my tables, I have to
possibly change my function headers rather than merely the code inside
them.

regards
Richard