Thread: BUG #6400: function arguments not accepted

The following bug has been logged on the website:

Bug reference:      6400
Logged by:          Sandra Knoch
Email address:      knoch@ipa.fraunhofer.de
PostgreSQL version: 9.0.6
Operating system:   Windows Server 2007 SP2
Description:=20=20=20=20=20=20=20=20

I have a strange problem regarding functions with parameters. The parameters
are not accepted and I always get the error column "user" does not exist,
where user is the parameter name and not a column. The function looks like
this:

CREATE OR REPLACE FUNCTION tissue.insert_biopsy_data
(
  "user"          integer,
  in_date         date,
  in_description  varchar,
  in_weight       numeric,
  in_size         numeric
)
RETURNS integer AS
$$
declare
result integer;
begin
  INSERT INTO tissue.biopsy (id_biopsy, f_user,introduction_date,
description, weight, size)
  VALUES
  (
  DEFAULT,
  user,
  in_date,
  in_description,
  in_weight,
  in_size
  );
  SELECT id_biopsy FROM tissue.biopsy INTO result;
  Return result;
end
$$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

It works with my old PostgreSQL 8.4 version, but not with the new one. What
is the problem here? Why is the argument not recognized as argument but as a
column name? Thank you for any help!

On Wed, Jan 18, 2012 at 5:38 AM,  <knoch@ipa.fraunhofer.de> wrote:
> =C2=A0INSERT INTO tissue.biopsy (id_biopsy, f_user,introduction_date,
> description, weight, size)
> =C2=A0VALUES
> =C2=A0(
> =C2=A0DEFAULT,
> =C2=A0user,
> =C2=A0in_date,
> =C2=A0in_description,
> =C2=A0in_weight,
> =C2=A0in_size
> =C2=A0);

What happens when you double-quote user in the insert statement?

On Wed, Jan 18, 2012 at 8:38 AM,  <knoch@ipa.fraunhofer.de> wrote:
> The following bug has been logged on the website:
>
> Bug reference: =A0 =A0 =A06400
> Logged by: =A0 =A0 =A0 =A0 =A0Sandra Knoch
> Email address: =A0 =A0 =A0knoch@ipa.fraunhofer.de
> PostgreSQL version: 9.0.6
> Operating system: =A0 Windows Server 2007 SP2
> Description:
>
> I have a strange problem regarding functions with parameters. The paramet=
ers
> are not accepted and I always get the error column "user" does not exist,
> where user is the parameter name and not a column. The function looks like
> this:
>
> CREATE OR REPLACE FUNCTION tissue.insert_biopsy_data
> (
> =A0"user" =A0 =A0 =A0 =A0 =A0integer,
> =A0in_date =A0 =A0 =A0 =A0 date,
> =A0in_description =A0varchar,
> =A0in_weight =A0 =A0 =A0 numeric,
> =A0in_size =A0 =A0 =A0 =A0 numeric
> )
> RETURNS integer AS
> $$
> declare
> result integer;
> begin
> =A0INSERT INTO tissue.biopsy (id_biopsy, f_user,introduction_date,
> description, weight, size)
> =A0VALUES
> =A0(
> =A0DEFAULT,
> =A0user,
> =A0in_date,
> =A0in_description,
> =A0in_weight,
> =A0in_size
> =A0);
> =A0SELECT id_biopsy FROM tissue.biopsy INTO result;
> =A0Return result;
> end
> $$
> LANGUAGE 'plpgsql'
> VOLATILE
> CALLED ON NULL INPUT
> SECURITY INVOKER
> COST 100;
>
> It works with my old PostgreSQL 8.4 version, but not with the new one. Wh=
at
> is the problem here? Why is the argument not recognized as argument but a=
s a
> column name? Thank you for any help!

There were some changes in the way variable references are resolved in
PL/pgsql in version 9.0; unfortunately, you seem to have run across
one of the cases where the new behavior requires more quoting than the
old one did.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company