Postgres version: 14

I have a script that does a number of small repetitive dynamic queries. This works, but it is slow, so I am converting them to prepared statements. This also works until I try to pass a column name as an argument at which point everything seems to be a syntax or "operator does not exist" error. What is the correct syntax to get this to work as shown here https://dev.to/aws-heroes/postgresql-prepared-statements-in-pl-pgsql-jl3 ?

DO
$$
BEGIN
    DECLARE rtmp1 record;   
    DECLARE rtmp2 record;   
    DECLARE rtmp3 record;   
    DECLARE col_name1 text := 'my_field1';
    DECLARE col_name2 text := 'my_field2';
    DECLARE col_name3 text := 'my_field3';

    -- PREPARE QUERIES
    DEALLOCATE ALL;
    EXECUTE FORMAT('PREPARE q_test(text) AS
        SELECT
            first_name
        FROM my_table
        WHERE $1 = 0');

    EXECUTE FORMAT('EXECUTE q_test(%s)', col_name1) INTO rtmp1;
    EXECUTE FORMAT('EXECUTE q_test(%s)', col_name2) INTO rtmp2;
    EXECUTE FORMAT('EXECUTE q_test(%s)', col_name3) INTO rtmp3;
END
$$;

CodePudding user response：

I can get the function to run:

\d my_table 
                           Table "public.my_table"
    Column    |            Type             | Collation | Nullable | Default 
-------------- ----------------------------- ----------- ---------- ---------
 other_column | character varying(100)      |           |          | 
 updated_at   | timestamp without time zone |           |          | 
 new_colum    | character varying(100)      |           |          | 
 first_name   | character varying           |           |          | 
 id           | integer                     |    

DO          
$$
BEGIN

    -- PREPARE QUERIES
    DEALLOCATE ALL;
    EXECUTE FORMAT('PREPARE q_test(text) AS
        SELECT
            first_name
        FROM my_table
        WHERE %I = 0', 'id');
END
$$;

UPDATE.

A version of function that iterates over field names and executes the query for each field name. Does away with the PREPARE/EXECUTE.

DO
$$
DECLARE
   fld_name text;
BEGIN

    FOREACH fld_name IN ARRAY array['id', 'first_name'] LOOP
        RAISE NOTICE '%', fld_name;
        EXECUTE FORMAT('SELECT
            first_name
        FROM my_table
        WHERE %I IS NOT NULL', fld_name);
    END LOOP;
END
$$;

NOTICE:  id
NOTICE:  first_name
DO