Обсуждение: function parameters : bug?
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
Richard Hayward wrote: > 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); > 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. So what would this do? SELECT inta+1 FROM mytable Agreed, it's tricky, but without some variable marker (e.g. $my_param) or removing inline SQL from the language, I'm not sure there's much to do. -- Richard Huxton Archonet Ltd