无法在另一个使用PLPGSQL的过程中调用带有循环的过程。

huangapple
go评论69阅读模式
英文:

Unable to call procedure inside another procedure with loop using PLPGSQL

问题

  1. SQL错误[42601]：错误：过程参数“response”是输出参数，但相应的参数不可写
  2. 位置：PL/pgSQL函数auto_save(character varying)的第182行，在CALL

尝试使用存储过程和循环将数据保存到表中，但无法解决此错误。我尝试将第二个过程重写为函数，但仍然出现相同的错误。

英文:

Trying to save data into table using procedures and loop, but unable to solve this error.

  1. 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)
  2.  LANGUAGE plpgsql
  3. AS $procedure$
  4. declare 
  5. begin
  6. if 1=1 then
  7. 	raise notice '_Insert start';
  9. 			insert into table_A
  10. 			(
  11. 			brand,
  12. 			model,
  13. 			year
  14. 			)
  15. 			values(	
  16. 			p_1,
  17. 			p_2,
  18. 			p_3
  19. 			);
  20. 			raise notice 'insert-end';
  21. else 
  22. 			select 'p_1,p_2 cannot be null'  into response;
  23. 		end if;
  24. 	
  25.    exception 
  26. 	when sqlstate '23505' then
  27. 		select 'Duplicate Record'  into response;
  28. 	--when others then 
  29. 	--	select '-1' into response;
  30.    end
  31.    $procedure$
  33. ********************************************************************************************
  34.    
  35. CREATE OR REPLACE PROCEDURE auto_save(INOUT response character varying DEFAULT '1'::character varying)
  36.  LANGUAGE plpgsql
  37. AS $procedure$
  38. declare
  39. response varchar(100);
  40. f record;
  41.  l record;
  42. m record;
  43. 	
  44. begin
  45. 	
  46. 	for f in select p_1,p_2,p_3 from table_dump
  47. 	 loop
  48. 	call public.quality_save(
  49. 					p_1 =>f.p_1,
  50. 					p_2 =>f.p_2, 
  51. 					p_3 =>f.p_3
  52. 						)
  53. 						;
  54. 	 
  55. 	 	select 1 into response;
  56. 	 end loop;	
  57. 	select 1 into response;
  58. end;$procedure$
  59. ;

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 参数传递参数。请像这样调用该过程：

  1. DECLARE
  2.    f 记录;
  3.    p_response 字符变化;
  4. BEGIN
  5.    [...]
  6.    调用 public.quality_save(
  7.            p_1 => f.p_1,
  8.            p_2 => f.p_2, 
  9.            p_3 => f.p_3,
  10.            response => p_response
  11.         );
  12.    [...]
英文:

You didn't pass an argument for response. Call the procedure like this:

  1. DECLARE
  2.    f record;
  3.    p_response character varying;
  4. BEGIN
  5.    [...]
  6.    CALL public.quality_save(
  7.            p_1 =>f.p_1,
  8.            p_2 =>f.p_2, 
  9.            p_3 =>f.p_3,
  10.            response => p_response
  11.         );
  12.    [...]

huangapple
  • 本文由 发表于 2023年2月14日 20:22:11
  • 转载请务必保留本文链接：https://go.coder-hub.com/75447763.html
  • plpgsql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定