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 best solutions below