Re: PQexecParams, placeholders and variable lists of params

Hi
A function cannot have an undefined signature, but can accept an array of
arguments:
CREATE OR REPLACE FUNCTION bpd.object_del_by_id_array(
object_array bigint[])
RETURNS SETOF bpd.errarg_action
LANGUAGE 'plpgsql'
COST 100
VOLATILE SECURITY DEFINER PARALLEL SAFE
ROWS 1000
SET search_path=bpd
AS $BODY$
DECLARE
cfg_desc "bpd"."cfg_action"%ROWTYPE;
action_entity RECORD;

action_result RECORD;
result "bpd"."errarg_action"%ROWTYPE;
BEGIN
SELECT * INTO cfg_desc FROM "bpd"."cfg_action" WHERE id = 'delete';

FOR action_entity IN SELECT id, "name" FROM bpd.object WHERE id =
ANY(object_array)
LOOP
action_result = "bpd"."object_del"(action_entity.id);
result."err_id" = action_result.outresult;
result."errdesc" = action_result.outdesc;
result."entity_id" = 20;
result."entity_instance_id" = action_entity.id;
result."entity_instance_name" = action_entity.name;
result."action_id" = cfg_desc."actid";
result."action_desc" = cfg_desc.desc;
RETURN NEXT result;
END LOOP;
END;
$BODY$;
--
Regards, Dmitry!

вт, 23 нояб. 2021 г. в 16:37, <tomas(at)tuxteam(dot)de>:

> Hi,
>
> PQexecParams expects a query string with "$1", "$2"... placeholders,
> which refer to as many params in the param list. This keeps SQL
> injection at bay.
>
> Is there a way to express "variable length" lists? IOW, if I want to do
> a query like
>
> "SELECT * FROM customers WHERE id IN ($1, $2) AND name like $3;"
>
> is there a way to do that without knowing beforehand how many values go
> into the IN list?
>
> It would be very welcome for you to rub my nose against the place in The
> Fine Manual where I could have found that :-)
>
> Thanks & cheers
> - tomás
>