PostgreSQL function error - help!

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 statement

Time: 0.003s

Thank you in advance for your help!

1 Like

You’re going to need to fix a bunch of these.

Postgres is case sensitive for its identifiers, but it hides that from you by converting identifiers to lowercase for you, unless they are quoted.

In your params, you specified “P2” (quoted), but when using it, you used P2, which was converted internally to p2, and that doesn’t exist.

Generally, unless you have a good reason, you should not quote your identifiers. The solution in this case is to remove all the quotes.

Thank you Kem!

I believe that PG Admin 4 did that for me silently. Navicat on the other hand, even if parameters in the editor are not quoted, quotes lowercase parameters, and the database accepts it. I will switch to Navicat for now. (edit: and of course code with lowercase parameters!)

I would never have guessed that. I questioned my code… Thank you again!

1 Like

I see your P2 defined as int8. The value „200002“ doesn‘t fit here.

Carsten;

The data saved in the table is correct. If I am not mistaken, INT4 (integer) can store numbers in the range -2147483648 to +2147483647. I believe that it is INT2 (smallint) that you were thinking about, which is limited to -32768 to +32767. INT8 (bigint) would accept -9223372036854775808 to 9223372036854775807.

You made me double check my data types, which is a good thing. Thank you!

OK, sorry, I‘ve never used extensions and interpreted the „int8“ as the same as in Xojo.

No worry my friend! I appreciate the help and your comment not only made me review my data types, but will also help someone else with the same who might read this thread in the future.