Re: pg_restore depending on user functions

Function A (not restore) first action:

CREATE OR REPLACE FUNCTION bpd.class_act_ext_by_id_parent(
iid_parent bigint)
    RETURNS SETOF bpd.vclass_ext
    LANGUAGE 'plpgsql'
    COST 100
    STABLE SECURITY DEFINER PARALLEL SAFE
    ROWS 1000

    SET search_path=bpd
AS $BODY$
DECLARE
    class_array BIGINT[]; --Массив объектов
BEGIN
    class_array = (SELECT array_agg(c.id) FROM ONLY bpd.class c WHERE c.id_parent = iid_parent);

    RETURN QUERY  
    SELECT
    c.id,
    c."timestamp",
    true AS has_active,
    c.timestamp_child_change,
    c.id_con,
    c.id_group,
    c.id_group_root,
    c.id_parent,
    c.timestamp_parent,
    c.id_root,
    c.timestamp_root,
    c.level,
    (c.level = 0) AS is_root,
    ((c.tableoid)::regclass)::character varying AS tablename,
    c.name,
    c.name_format,
    c.quantity_show,
    c."desc",
    c."on",
    c.on_extensible,
    c.on_abstraction,
    c.id_unit,
    c.id_unit_conversion_rule,
    c.barcode_manufacturer,
    c.barcode_local,
    (EXISTS ( SELECT 1
           FROM ONLY bpd.class cc
          WHERE (cc.id_parent = c.id))) AS include_child_class,
    (EXISTS ( SELECT 1
           FROM bpd.object co
          WHERE ((co.id_class = c.id) AND (co.timestamp_class = c."timestamp")))) AS include_child_object,
    (EXISTS ( SELECT 1
           FROM ONLY bpd.class cc
          WHERE ((cc.id_parent = c.id) AND (NOT cc.on_abstraction)))) AS include_child_real_class,
    (EXISTS ( SELECT 1
           FROM ONLY bpd.class cc
          WHERE ((cc.id_parent = c.id) AND cc.on_abstraction))) AS include_child_abstract_class,
    ( SELECT count(1) AS count
           FROM ONLY bpd.class cc
          WHERE (cc.id_parent = c.id)) AS child_count,
    (con.group_recycle = c.id_group_root) AS in_recycle,
    c.on_freeze,
    cp_list.property_list,
    c_ready.ready,
    c_path.path
   FROM ((((ONLY bpd.class c
     LEFT JOIN bpd.conception con ON ((con.id = c.id_con)))
     LEFT JOIN "bpd"."int_class_ext_prop_by_id_class_array"(class_array) cp_list ON ((cp_list.id = c.id)))
     LEFT JOIN "bpd"."int_class_ready_by_id_class_array"(class_array) c_ready ON ((c_ready.id = c.id)))
     LEFT JOIN "bpd"."int_class_path_by_id_class_array"(class_array) c_path ON ((c_path.id = c.id)))
    WHERE c.id = ANY(class_array)
    ORDER BY "name";
END;
$BODY$;

Function B:

CREATE OR REPLACE FUNCTION bpd.int_class_ext_prop_by_id_class_array(
class_array bigint[])
    RETURNS SETOF bpd.int_class_ext
    LANGUAGE 'sql'
    COST 100
    STABLE PARALLEL SAFE
    ROWS 1000
AS $BODY$
    SELECT cp.id_class AS id,
    array_agg((cp.*)::bpd.cclass_prop ORDER BY cp.sort) AS property_list
    FROM bpd.vclass_prop cp
    WHERE (cp.id_class = ANY(class_array))
    GROUP BY cp.id_class;
$BODY$;

CREATE OR REPLACE FUNCTION bpd.int_class_ready_by_id_class_array(
class_array bigint[])
    RETURNS SETOF bpd.int_class_ready
    LANGUAGE 'sql'
    COST 100
    STABLE PARALLEL SAFE
    ROWS 1000

AS $BODY$
SELECT
        c.id,
        CASE c.on_abstraction
            WHEN false THEN
            CASE bpd.int_class_format_check(c.id)
                WHEN true THEN
                CASE (count(cp.id) > 0)
                    WHEN true THEN bool_and(
                    CASE cp.id_prop_type
                        WHEN 1 THEN (
                        CASE cp.id_data_type
                            WHEN 1 THEN (sv.val_varchar IS NOT NULL)
                            WHEN 2 THEN (sv.val_int IS NOT NULL)
                            WHEN 3 THEN (sv.val_numeric IS NOT NULL)
                            WHEN 4 THEN (sv.val_real IS NOT NULL)
                            WHEN 5 THEN (sv.val_double IS NOT NULL)
                            WHEN 6 THEN (sv.val_money IS NOT NULL)
                            WHEN 7 THEN (bv.val_text IS NOT NULL)
                            WHEN 8 THEN (bv.val_bytea IS NOT NULL)
                            WHEN 9 THEN (sv.val_boolean IS NOT NULL)
                            WHEN 10 THEN (sv.val_date IS NOT NULL)
                            WHEN 11 THEN (sv.val_time IS NOT NULL)
                            WHEN 12 THEN (sv.val_interval IS NOT NULL)
                            WHEN 13 THEN (sv.val_timestamp IS NOT NULL)
                            WHEN 14 THEN (bv.val_json IS NOT NULL)
                            WHEN 15 THEN (sv.val_bigint IS NOT NULL)
                            ELSE false
                        END OR cp.on_override)
                        WHEN 2 THEN (
                        CASE cp.id_data_type
                            WHEN 1 THEN (pev.val_varchar IS NOT NULL)
                            WHEN 3 THEN (pev.val_numeric IS NOT NULL)
                            ELSE false
                        END OR (cp.on_override AND (COALESCE(ev.id_prop_enum, (0)::bigint) > 0)))
                        WHEN 3 THEN (COALESCE(class_val.id, (0)::bigint) > 0)
                        WHEN 4 THEN ((COALESCE(lv.id_entity_instance, (0)::bigint) > 0) OR (cp.on_override AND (lv.id_entity IS NOT NULL)))
                        ELSE false
                    END)
                    ELSE true
                END
                ELSE false
            END
            ELSE false
        END AS ready
   FROM ONLY bpd.class c
     LEFT JOIN ONLY bpd.class_prop cp ON (c.id = cp.id_class)
     LEFT JOIN ONLY bpd.class_prop_user_small_val sv ON (sv.id_class_prop = cp.id)
     LEFT JOIN ONLY bpd.class_prop_user_big_val bv ON (bv.id_class_prop = cp.id)
     LEFT JOIN ONLY bpd.class_prop_enum_val ev ON (ev.id_class_prop = cp.id)
     LEFT JOIN ONLY bpd.prop_enum_val pev ON (pev.id = ev.id_prop_enum_val)
     LEFT JOIN ONLY bpd.class_prop_link_val lv ON (lv.id_class_prop = cp.id)
     LEFT JOIN ONLY bpd.class_prop_obj_val_class ov ON (ov.id_class_prop = cp.id)
     LEFT JOIN ONLY bpd.class class_val ON (class_val.id = ov.id_class_val)
    WHERE  (c.id = ANY(class_array))
  GROUP BY c.id;
$BODY$;

CREATE OR REPLACE FUNCTION bpd.int_class_path_by_id_class_array(
class_array bigint[])
    RETURNS SETOF bpd.int_class_path
    LANGUAGE 'plpgsql'
    COST 100
    STABLE PARALLEL SAFE
    ROWS 1000

    SET search_path=bpd
AS $BODY$
DECLARE
BEGIN
    RETURN QUERY WITH RECURSIVE rgroup(id_path, id, id_parent, level, path, spath, cycle) AS (
         SELECT rg.id,
            rg.id,
            rg.id_parent,
            0,
            ARRAY[rg.id] AS "array",
            concat(rg.name) AS concat,
            false AS bool
           FROM bpd."group" rg
           WHERE rg.id IN (SELECT ac.id_group FROM ONLY bpd.class ac WHERE ac.id = ANY(class_array))
        UNION ALL
         SELECT rgroup_1.id_path,
            rgc.id,
            rgc.id_parent,
            (rgroup_1.level + 1),
            (ARRAY[rgc.id] || rgroup_1.path),
            concat(rgc.name, $$\$$, rgroup_1.spath) AS concat,
            (rgc.id = ANY (rgroup_1.path))
           FROM (bpd."group" rgc
             JOIN rgroup rgroup_1 ON ((rgroup_1.id_parent = rgc.id)))
          WHERE (NOT rgroup_1.cycle)
        ),
        grouppath(id_path, spath) AS (
         SELECT DISTINCT rg.id_path,
            first_value(rg.spath) OVER (PARTITION BY rg.id_path ORDER BY rg.level DESC) AS first_value
           FROM rgroup rg
        ),
        rclass(id_path, id, id_parent, level, path, spath, cycle) AS (
         SELECT rc.id,
            rc.id,
            rc.id_parent,
            0,
            ARRAY[rc.id] AS "array",
            concat(rc.name) AS concat,
            false AS bool
           FROM ONLY bpd.class rc
           WHERE  (rc.id = ANY(class_array))
        UNION ALL
         SELECT rclass_1.id_path,
            rcc.id,
            rcc.id_parent,
            (rclass_1.level + 1),
            (ARRAY[rcc.id] || rclass_1.path),
            concat(rcc.name, $$\$$, rclass_1.spath) AS concat,
            (rcc.id = ANY (rclass_1.path))
           FROM (ONLY bpd.class rcc
             JOIN rclass rclass_1 ON ((rclass_1.id_parent = rcc.id)))
          WHERE (NOT rclass_1.cycle)
        ), classpath(id_path, spath) AS (
         SELECT DISTINCT rc.id_path,
            first_value(rc.spath) OVER (PARTITION BY rc.id_path ORDER BY rc.level DESC) AS first_value
           FROM rclass rc
        )
 SELECT c.id,
    concat(COALESCE(gp.spath, ''::text), '\\', COALESCE(cp.spath, ''::text)) AS path
   FROM ((ONLY bpd.class c
     LEFT JOIN grouppath gp ON ((gp.id_path = c.id_group)))
     LEFT JOIN classpath cp ON ((cp.id_path = c.id)))
   WHERE  (c.id = ANY(class_array));  
END;
$BODY$;

for compatibility with NPGSQL I had to create a mirrored composite type:

CREATE TYPE bpd.cclass_prop AS
(
id bigint,
id_class bigint,
timestamp_class timestamp without time zone,
on_inherit boolean,
inheritance boolean,
id_prop_inherit bigint,
timestamp_class_inherit timestamp without time zone,
id_prop_type integer,
id_data_type integer,
name character varying,
"desc" character varying,
sort integer,
on_override boolean,
on_val boolean,
string_val character varying,
tablename character varying,
ready boolean,
id_conception bigint,
id_class_definition bigint,
timestamp_class_definition timestamp without time zone,
id_prop_definition bigint,
on_override_prop_inherit boolean,
on_global boolean,
id_global_prop bigint,
tag character varying
);

CREATE CAST (bpd.vclass_prop AS bpd.cclass_prop)
WITH FUNCTION int_cast_vclass_prop_to_cclass_prop(ivclass_prop bpd.vclass_prop);

CREATE OR REPLACE FUNCTION bpd.int_cast_vclass_prop_to_cclass_prop(
ivclass_prop bpd.vclass_prop)
    RETURNS bpd.cclass_prop
    LANGUAGE 'plpgsql'
    COST 100
    IMMUTABLE PARALLEL SAFE
AS $BODY$
DECLARE
 result  "bpd"."cclass_prop"%ROWTYPE;
BEGIN
     result = ivclass_prop;
     RETURN result;
END;
$BODY$;