value for insert on a procedure call is taken as column name generating an error that reports such column doesn't exist

25 views Asked by At

I'm pretty sure I'm missing something simple here (hopefully) but I can't figure out what it is so any help will be very much appreciated.

I have the following procedure

CREATE OR REPLACE PROCEDURE schema_name.procedure_to_insert(
    in input_schema character varying,
    IN input_name character varying,
    in input_data character varying,
    IN input_hash character varying,
    IN input_description text,
    IN input_user_id int2
)
LANGUAGE plpgsql
AS $procedure$
    begin
        execute format('
            insert 
                into %1$I.table_name (
                    col_name,
                    col_data,
                    col_hash,
                    col_description,
                    col_user_id
                )
                values(
                    %2$s,
                    %3$s,
                    %4$s,
                    %5$s,
                    %6$s
                )
                on conflict (name) do nothing;
        ', input_schema, input_name, input_data, input_hash, input_description, input_user_id );
    end;
$procedure$
;

I call that procedure as follows:

call schema_name.procedure_to_insert(
    'val_schema',
    'val_name',
    'val_data',
    'val_hash',
    'val_desc',
    '1'
);

The table where the insert has to happen has the previously mentioned fields plus some other fields with default values

Running the call produces the following error message:

SQL Error [42703]: ERROR: column "val_name" does not exist Where: PL/pgSQL function procedure_to_insert(character varying,character varying,character varying,character varying,text,smallint) line 3 at EXECUTE

Replacing the values part of the procedure for the following gives the same error:

        values(
            input_name,
            input_data,
            input_hash,
            input_description,
            input_user_id
        )
0

There are 0 answers