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
)