How to resolve error when running dynamic PostreSQL function

Question

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;

First difference between "dynamic" and "manual" versions - in "dynamic" you use text data type for params, in "manual" - varchar. This types are not the same. Second - in "manual" you enclose the textual values in apostrophes, but in "dynamic" - do not. And I think the second is the cause of error - without apostrophes ' || huc || '::text will become MYHUC4::text, that's can be treated as a column name with conversion to text data type. The same issue with ' || email || '::text — Alexey
– Alexey, Commented Jun 30, 2022 at 17:50
I just amended the code. Check my changes about the hardcoded version. Are you saying I need to add additional apostrophes? Or just remove the text casting when I plug in the parameters? — gwydion93
– gwydion93, Commented Jun 30, 2022 at 18:11
1) Provide the code for my_funct(). 2) column "myhu4" does not exist is happening because the value for huc is being double quoted at some point e.g. "myhu4" and that is being taken as a column identifier. This why we need to 1) to be done. — Adrian Klaver
– Adrian Klaver, Commented Jun 30, 2022 at 18:19
Why do you even use dynamic SQL with EXECUTE in the first place? You do not want to construct a dynamic query here. Just SELECT * FROM my_app.my_funct(huc, id, email);! — Bergi
– Bergi, Commented Jun 30, 2022 at 19:15

Pepe N O · Accepted Answer · 2022-06-30 18:44:02Z

1

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

edited Jun 30, 2022 at 18:44

answered Jun 30, 2022 at 18:37

Pepe N O

2,3841 gold badge12 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gwydion93 Over a year ago

This actually worked quite well. I didn't realize || was the old way of doing things. This was much more compact. Thanks.

Collectives™ on Stack Overflow

How to resolve error when running dynamic PostreSQL function

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related