Re: function INPUT as whole table - Mailing list pgsql-sql

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.