I am using a function to either create or update records in a table. I am getting an error that P2 (my third parameter, key to the table) does not exist. I have now statred at it for several hours without figuring out what is incorrect. So, here is the function:
-
the record exists, so the issue is happening in the update part of the function
CREATE OR REPLACE FUNCTION “public”.“cust_manage_general_data”(“P0” bpchar, “P1” varchar, “P2” int8, “P3” bool, “P4” bpchar, “P5” bool, “P6” varchar, “P7” varchar, “P8” bpchar, “P9” varchar, “P10” bpchar, “P11” varchar, “P12” int8, “P13” int4, “P14” varchar, “P15” varchar, “p16” varchar, “P17” varchar, “P18” varchar, “P19” varchar, “P20” varchar, “P21” varchar, “P22” varchar, “P23” int8, “P24” varchar, “P25” int4)
RETURNS “pg_catalog”.“void” AS $BODY$BEGIN
LOOP
– first try to update the key
UPDATE dcligen SET cree = P0, creepar = P1, delflag = P3, devise = P4, isperson = P5, langue = P6, mail = P7, modifie = P8, modifiepar = P9, naissance = P10, nom = P11, npvc = P12, oldnum = P13, parttype = P14, prenom = P15, genre = P16, taxjur = P17, teld = P18, telm = P19, telt = P20, teltp = P21, accgrp = P22, adrnr = P23, mail2 = P24, circuit = P25 WHERE custid = P2 ;
IF found THEN
RETURN;
END IF;BEGIN INSERT INTO dcligen(cree, creepar, custid, delflag, devise, isperson, langue, mail, modifie, modifiepar, naissance, nom, npvc, oldnum, parttype, prenom, genre, taxjur, teld, telm, telt, teltp, accgrp, adrnr, mail2, circuit) VALUES (P0, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12, P13, P14, P15, P16, P17, P18, P19, P20, P21, P22, P23, P24, P25); RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing = and loop to try the UPDATE again (concurrent insert of the same key) END; END LOOP;END;$BODY$
And here is the string passed from my application:
Select "cust_manage_general_data"( cast('2008-09-01' as character(10)), cast('Louis Desjardins' as character varying), '200002', 'false', cast(' ' as character(3)), 'true', cast('FRA' as character varying), cast('catherine@catherinemail.ca' as character varying), cast('2021-02-27' as character(10)), cast('Louis Desjardins' as character varying), cast('2021-02-27' as character(10)), cast('Desjardins' as character varying), '8501', '2', cast('ATHL' as character varying), cast('Catherine' as character varying), cast('F' as character varying), cast('' as character varying), cast('(123) 456-7890' as character varying), cast('(123) 123-4567' as character varying), cast('' as character varying), cast('' as character varying), cast('SHIPTO' as character varying), '2', cast('' as character varying), '0' );
I am hoping that someone can spot the error that I have been missing. Probably something obvious…
Here is the debug run report:
Select “cust_manage_general_data”( cast(‘2008-09-01’ as character(10)), cast(‘Louis Desjardins’ as character varying), ‘200002’, ‘false’, cast(’ ’ as character(3)), ‘true’, cast(‘FRA’ as character varying), cast(‘catherine@catherinemail.ca’ as character varying), cast(‘2021-02-27’ as character(10)), cast(‘Louis Desjardins’ as character varying), cast(‘2021-02-27’ as character(10)), cast(‘Desjardins’ as character varying), ‘8501’, ‘2’, cast(‘ATHL’ as character varying), cast(‘Catherine’ as character varying), cast(‘F’ as character varying), cast(‘’ as character varying), cast(‘(123) 456-7890’ as character varying), cast(‘(123) 123-4567’ as character varying), cast(‘’ as character varying), cast(‘’ as character varying), cast(‘SHIPTO’ as character varying), ‘2’, cast(‘’ as character varying), ‘0’ )
ERROR: column “p2” does not exist
LINE 1: …2, adrnr = P23, mail2 = P24, circuit = P25 WHERE custid = P2
^
QUERY: UPDATE dcligen SET cree = P0, creepar = P1, delflag = P3, devise = P4, isperson = P5, langue = P6, mail = P7, modifie = P8, modifiepar = P9, naissance = P10, nom = P11, npvc = P12, oldnum = P13, parttype = P14, prenom = P15, genre = P16, taxjur = P17, teld = P18, telm = P19, telt = P20, teltp = P21, accgrp = P22, adrnr = P23, mail2 = P24, circuit = P25 WHERE custid = P2
CONTEXT: PL/pgSQL function cust_manage_general_data(character,character varying,bigint,boolean,character,boolean,character varying,character varying,character,character varying,character,character varying,bigint,integer,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,character varying,bigint,character varying,integer) line 4 at SQL statementTime: 0.003s
Thank you in advance for your help!