function parameters : bug?

From: Richard Hayward <richard(at)tortoise(dot)demon(dot)co(dot)uk>
To: pgsql-sql(at)postgresql(dot)org
Subject: function parameters : bug?
Date: 2005-07-10 10:52:10
Message-ID: ugu1d19614tuteki2jgm0496d58kcggdhn@4ax.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Harald Fuchs 2005-07-10 12:19:56 Re: Make COUNT(*) Faster?
Previous Message Mischa Sandberg 2005-07-10 01:00:36 Re: getting back autonumber just inserted