Re: PQexecParams, placeholders and variable lists of params

Поиск
Список
Период
Сортировка
От Дмитрий Иванов
Тема Re: PQexecParams, placeholders and variable lists of params
Дата
Msg-id CAPL5KHofiPEWqHx7a68pHAK=mOVSN8v1H8WnzghHPDJcWR3y3w@mail.gmail.com
обсуждение исходный текст
Ответ на PQexecParams, placeholders and variable lists of params  (<tomas@tuxteam.de>)
Ответы Re: PQexecParams, placeholders and variable lists of params
Список pgsql-general
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@tuxteam.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

В списке pgsql-general по дате отправления:

Предыдущее
От: Matthias Apitz
Дата:
Сообщение: Re: SELECT fails to present result rows depending on the columns to show
Следующее
От:
Дата:
Сообщение: Re: Regex for Word space Word space Word ....