Re: function INPUT as whole table

This is true. But there is a useful trick. When sampling a moderate amount
of data, you can use an array as an argument. This allows for prefetching
from a large dataset and restricting data binding.

CREATE OR REPLACE FUNCTION
bpd.int_object_ext_prop_by_id_object_array(*object_array
bigint[]*)
RETURNS SETOF bpd.int_object_ext
LANGUAGE plpgsql
*STABLE PARALLEL SAFE*
AS $function$
DECLARE

BEGIN
RETURN QUERY SELECT
op.id_object_carrier AS id,
array_agg((op.*)::bpd.cobject_prop) AS property_list
FROM bpd.vobject_prop op
WHERE (op.id_object_carrier = *ANY(object_array)*)
GROUP BY op.id_object_carrier;
END;
$function$;

---------------------------------------
CREATE OR REPLACE FUNCTION
bpd.object_prop_user_small_agg_func_find_ext(iid_global_prop bigint,
find_mask character varying)
RETURNS SETOF bpd.vobject_general_ext
LANGUAGE plpgsql
*STABLE PARALLEL SAFE*
AS $function$
DECLARE
object_array BIGINT[]; --Массив объектов
fglobal_prop "bpd"."vglobal_prop"%ROWTYPE; --Глобальное свойство
BEGIN
SELECT * INTO fglobal_prop FROM ONLY "bpd"."vglobal_prop" WHERE id =
iid_global_prop;
IF NOT(fglobal_prop IS NULL) THEN
CASE fglobal_prop.id_prop_type
WHEN 1 THEN
*object_array* = (SELECT array_agg(o.id) ...)
);
WHEN 2 THEN
find_mask = bpd.int_is_numberic(find_mask);
*object_array* = (SELECT array_agg(o.id) ...)
END CASE;

RETURN QUERY
SELECT
o.id,
o.name,
o.id_conception,
...
o_ext.property_list,
p_path.path,
cr.round
FROM bpd.object o
JOIN bpd.class_snapshot cp ON (o."id" = ANY(object_array)) AND
(cp.id = o.id_class) AND (cp."timestamp" = o.timestamp_class)
LEFT JOIN bpd.conception con ON (con.id = o.id_conception)
LEFT JOIN bpd.unit_conversion_rules cr ON (cr.id =
o.id_unit_conversion_rule)
LEFT JOIN bpd.int_position_path p_path ON (p_path.id =
o.id_position)
LEFT JOIN*
bpd.int_object_ext_prop_by_id_object_array(object_array)* o_ext ON (o_ext.id
= o.id)
ORDER BY o.name;
END IF;
END;
$function$;
-- -------------------------------------------------------------
In this way, I managed to significantly increase productivity.

вт, 15 июн. 2021 г. в 17:41, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>:

>
> On Tuesday, June 15, 2021, Yambu <hyambu(at)gmail(dot)com> wrote:
>
>> Hello
>>
>> Is it possible to have as input to a function , a whole table contents,
>> if the table is a small lookup table?
>>
>> Something like this
>>
>> CREATE or replace function test (IN (select * from table1) )
>>
>
> No. Function arguments are data types, not contents. Queries go inside
> the function body.
>
> David J.
>
>
>