PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15794
PG Version10.7
OSwindows 10 Home
Opened2019-05-07 08:46:03+00
Reported byDada Zhang
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15794
Logged by:          Dada Zhang
Email address:      (redacted)
PostgreSQL version: 10.7
Operating system:   windows 10 Home
Description:        

( I am not good at English, English is not my native language, please excuse
typing errors. )

When the parameter name of the stored procedure is the same as the table
field name of the update statement, a problem is caused: "field association
is ambiguous."

Such as:

There have a table and some record:
```
CREATE TABLE "public"."student" (
  "id" int4 NOT NULL,
  "name" varchar(255),
  PRIMARY KEY ("id")
);
insert into "public"."student"(id,name) VALUES (1,'Zhang San');
insert into "public"."student"(id,name) VALUES (2,'Li Si');
```

There have a function (it will caused:  Field association \"id\" is
ambiguous)
```
CREATE OR REPLACE FUNCTION public.update_student(
	id integer,
	name character varying)
    RETURNS "pg_catalog"."void"
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
BEGIN
	UPDATE public.student SET name = update_student.name
	WHERE id = update_student.id;
END; 
$BODY$;
```

But, when we change the function: (it will be ok, the sql execute
successed)
```
CREATE OR REPLACE FUNCTION public.update_student(
	_id integer,
	name character varying)
    RETURNS "pg_catalog"."void"
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
BEGIN
	UPDATE public.student SET name = update_student.name
	WHERE id = _id;
END; 
$BODY$;
```
or
(this function also have a problem, PostgreSQL after 9, SQL string escape is
cumbersome)
```
CREATE OR REPLACE FUNCTION public.update_student(
	_id integer,
	name character varying)
    RETURNS "pg_catalog"."void"
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$
DECLARE
    v_sql varchar;
BEGIN
	v_sql := 'UPDATE public.student SET name = ' || E'\'' ||
update_student.name || E'\'' || 
	               ' WHERE id = ' || E'\'' || id || E'\'' || ';';
	EXECUTE v_sql;
END; 
$BODY$;
```

The above. thank you.

Messages

DateAuthorSubject
2019-05-07 08:46:03+00PG Bug reporting formBUG #15794: Defects regarding stored procedure parameters
2019-05-07 19:14:59+00"David G(dot) Johnston"Re: BUG #15794: Defects regarding stored procedure parameters