pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

function parameters : bug?
2+ messages / 2 participants
[nested] [flat]

* function parameters : bug?
@ 2005-07-10 10:52  Richard Hayward <richard@tortoise.demon.co.uk>
  0 siblings, 1 reply; 2+ messages in thread

From: Richard Hayward @ 2005-07-10 10:52 UTC (permalink / raw)
  To: 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







^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: function parameters : bug?
@ 2005-07-14 08:14  Richard Huxton <dev@archonet.com>
  parent: Richard Hayward <richard@tortoise.demon.co.uk>
  0 siblings, 0 replies; 2+ messages in thread

From: Richard Huxton @ 2005-07-14 08:14 UTC (permalink / raw)
  To: Richard Hayward <richard@tortoise.demon.co.uk>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2005-07-14 08:14 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-10 10:52 function parameters : bug? Richard Hayward <richard@tortoise.demon.co.uk>
2005-07-14 08:14 ` Richard Huxton <dev@archonet.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox