How to resolve error when running dynamic PostreSQL function-CodePudding

I have a function that takes 3 parameters: huc, id_list, and email.

 CREATE OR REPLACE FUNCTION my_app.job_batch(
    huc text,
    input_list text[],
    email text
 ) RETURNS VOID AS
 $$
 DECLARE
     id text;
 BEGIN
     FOREACH id IN ARRAY input_list LOOP
         EXECUTE 'SELECT * FROM my_app.my_funct(
         ' || huc || '::text,
         ' || id || '::text,
         ' || email || '::text)';
     END LOOP;
 END;
 $$
 LANGUAGE plpgsql;

When I try to run the function however, it throws an error: ERROR: column "myhu4" does not exist

SELECT * FROM spg_app.append_spg_job_batch('MYHUC4', array['1021', '1025','1026','1027','0701','0702','0703','0708','0709'], '[email protected]');

Why is it referring to myhuc4 as a column and why is displaying it in lower case. Is my syntax below to run the function with those 3 parameters incorrect? Note: If I run the below hardcoded version, it runs fine:

DO $$
DECLARE
    id_list text[] := array['1021', '1025','1026','1027','0701','0702','0703','0708','0709'];
    id text;
BEGIN
    FOREACH id in ARRAY id_list LOOP
        EXECUTE 'SELECT * FROM my_app.my_funct(
        ''MYHU4''::text,
        ' || id || '::text,
        ''[email protected]''::text)'
    END LOOP;
END;
$$
LANGUAGE plpgsql;

CodePudding user response：

I suggest to use parameters instead of bad practice of stitching strings, as follows:

CREATE OR REPLACE FUNCTION my_app.job_batch(
    huc text,
    input_list text[],
    email text
 ) RETURNS VOID AS
 $$
 DECLARE
     id text;
 BEGIN
     FOREACH id IN ARRAY input_list LOOP
         execute format ('SELECT * FROM my_app.my_funct($1, $2, $3)')
            using huc, id, email;
     END LOOP;
 END;
 $$
 LANGUAGE plpgsql;

as shown in official docs https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN