英文:
Unable to call procedure inside another procedure with loop using PLPGSQL
问题
SQL错误[42601]：错误：过程参数“response”是输出参数，但相应的参数不可写
位置：PL/pgSQL函数auto_save(character varying)的第182行，在CALL
尝试使用存储过程和循环将数据保存到表中，但无法解决此错误。我尝试将第二个过程重写为函数，但仍然出现相同的错误。
英文:
Trying to save data into table using procedures and loop, but unable to solve this error.
CREATE OR REPLACE PROCEDURE quality_save(p_1 character varying, p_2 character varying, p_3 character varying DEFAULT NULL::character varying, INOUT response character varying DEFAULT '1'::character varying)
LANGUAGE plpgsql
AS $procedure$
declare
begin
if 1=1 then
raise notice '_Insert start';
insert into table_A
(
brand,
model,
year
)
values(
p_1,
p_2,
p_3
);
raise notice 'insert-end';
else
select 'p_1,p_2 cannot be null' into response;
end if;
exception
when sqlstate '23505' then
select 'Duplicate Record' into response;
--when others then
-- select '-1' into response;
end
$procedure$
********************************************************************************************
CREATE OR REPLACE PROCEDURE auto_save(INOUT response character varying DEFAULT '1'::character varying)
LANGUAGE plpgsql
AS $procedure$
declare
response varchar(100);
f record;
l record;
m record;
begin
for f in select p_1,p_2,p_3 from table_dump
loop
call public.quality_save(
p_1 =>f.p_1,
p_2 =>f.p_2,
p_3 =>f.p_3
)
;
select 1 into response;
end loop;
select 1 into response;
end;$procedure$
;
SQL Error [42601]: ERROR: procedure parameter "response" is an output parameter but corresponding argument is not writable
Where: PL/pgSQL function auto_save(character varying) line 182 at CALL
I tried rewriting second procedure as function but still giving the same error.
答案1
得分: 1
你没有为 response 参数传递参数。请像这样调用该过程：
DECLARE
f 记录;
p_response 字符变化;
BEGIN
[...]
调用 public.quality_save(
p_1 => f.p_1,
p_2 => f.p_2,
p_3 => f.p_3,
response => p_response
);
[...]
英文:
You didn't pass an argument for response. Call the procedure like this:
DECLARE
f record;
p_response character varying;
BEGIN
[...]
CALL public.quality_save(
p_1 =>f.p_1,
p_2 =>f.p_2,
p_3 =>f.p_3,
response => p_response
);
[...]
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论