From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | 740084020(at)qq(dot)com |
Subject: | BUG #15794: Defects regarding stored procedure parameters |
Date: | 2019-05-07 08:46:03 |
Message-ID: | 15794-317cc14fba9a1d36@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 15794
Logged by: Dada Zhang
Email address: 740084020(at)qq(dot)com
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2019-05-07 10:12:27 | Re: BUG #15793: Required Community Version Installs not the customized EnterpriseDB one. |
Previous Message | Amit Langote | 2019-05-07 08:10:34 | Re: BUG #15672: PostgreSQL 11.1/11.2 crashed after dropping a partition table |