Re: pg_restore depending on user functions - Mailing list pgsql-bugs

It turns out that everything was rolling like a snowball, after the wrong order of CAST creation

--Line 185:
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
);

--Line 4085:
CREATE TABLE bpd.conception (
id bigint NOT NULL,
    name character varying(100) NOT NULL,
    "on" boolean NOT NULL,
    "desc" character varying(2044),
    "default" boolean DEFAULT false NOT NULL,
    pos_recycle bigint DEFAULT '-1'::integer NOT NULL,
    pos_temp_recycle bigint DEFAULT '-1'::integer NOT NULL,
    "timestamp" timestamp without time zone DEFAULT LOCALTIMESTAMP NOT NULL,
    group_recycle bigint DEFAULT '-1'::integer NOT NULL,
    on_root_create boolean DEFAULT true NOT NULL,
    actcatalog integer DEFAULT 1 NOT NULL
);

--Line 4374:
CREATE TABLE bpd.class_prop (
id bigint NOT NULL,
    id_prop_inherit bigint DEFAULT '-1'::integer NOT NULL,
    id_class bigint DEFAULT 0 NOT NULL,
    timestamp_class timestamp without time zone NOT NULL,
    id_data_type integer NOT NULL,
    name character varying(100) NOT NULL,
    "desc" character varying(2044) NOT NULL,
    inheritance boolean NOT NULL,
    sort integer DEFAULT 0 NOT NULL,
    on_inherit boolean NOT NULL,
    on_override boolean NOT NULL,
    id_prop_type integer NOT NULL,
    timestamp_class_inherit timestamp without time zone,
    id_conception bigint NOT NULL,
    id_prop_definition bigint DEFAULT '-1'::integer NOT NULL,
    id_class_definition bigint DEFAULT '-1'::integer NOT NULL,
    timestamp_class_definition timestamp without time zone,
    tag character varying DEFAULT ''::character varying NOT NULL
);

--Line 4403:
CREATE TABLE bpd.class_prop_enum_val (
id_class_prop bigint NOT NULL,
    id_class bigint NOT NULL,
    timestamp_class timestamp without time zone NOT NULL,
    id_prop_enum bigint NOT NULL,
    id_prop_enum_val bigint,
    inheritance boolean NOT NULL
);

--Line 4420:
CREATE TABLE bpd.class_prop_link_val (
id_class_prop bigint NOT NULL,
    id_class bigint NOT NULL,
    timestamp_class timestamp without time zone NOT NULL,
    id_entity integer NOT NULL,
    id_entity_instance bigint,
    inheritance boolean NOT NULL,
    id_sub_entity_instance bigint DEFAULT '-1'::integer
);

--Line 4438:
CREATE TABLE bpd.class_prop_obj_val_class (
id bigint NOT NULL,
    id_class bigint NOT NULL,
    timestamp_class timestamp without time zone NOT NULL,
    id_class_prop bigint NOT NULL,
    id_class_val bigint,
    bquantity_max numeric DEFAULT '-1'::integer NOT NULL,
    bquantity_min numeric DEFAULT '-1'::integer NOT NULL,
    timestamp_class_val timestamp without time zone,
    embed_mode integer DEFAULT 0 NOT NULL,
    embed_single boolean DEFAULT true NOT NULL,
    embed_class_real_id bigint DEFAULT '-1'::integer NOT NULL,
    id_unit_conversion_rule integer DEFAULT '-1'::integer NOT NULL
);

--Line 4484:
CREATE TABLE bpd.class_prop_user_small_val (
id_class_prop bigint NOT NULL,
    timestamp_class timestamp without time zone NOT NULL,
    val_int integer,
    val_boolean boolean,
    val_varchar character varying(2044),
    val_real real,
    val_numeric numeric,
    val_date date,
    val_time time without time zone,
    val_interval interval,
    val_timestamp timestamp without time zone,
    val_money money,
    val_double double precision,
    max_val numeric NOT NULL,
    round integer NOT NULL,
    id_class bigint NOT NULL,
    id_data_type integer NOT NULL,
    inheritance boolean NOT NULL,
    val_bigint bigint,
    min_val numeric DEFAULT 0 NOT NULL,
    max_on boolean DEFAULT false NOT NULL,
    min_on boolean DEFAULT false NOT NULL,
    round_on boolean DEFAULT false NOT NULL
);

--Line 4461:
CREATE TABLE bpd.class_prop_user_big_val (
id_class_prop bigint NOT NULL,
    timestamp_class timestamp without time zone NOT NULL,
    val_int integer,
    val_boolean boolean,
    val_varchar character varying(2044),
    val_real real,
    val_numeric numeric,
    val_date date,
    val_time time without time zone,
    val_interval interval,
    val_timestamp timestamp without time zone,
    val_money money,
    val_double double precision,
    max_val numeric NOT NULL,
    round integer NOT NULL,
    id_class bigint NOT NULL,
    id_data_type integer NOT NULL,
    inheritance boolean NOT NULL,
    val_bigint bigint,
    min_val numeric DEFAULT 0 NOT NULL,
    max_on boolean DEFAULT false NOT NULL,
    min_on boolean DEFAULT false NOT NULL,
    round_on boolean DEFAULT false NOT NULL
);

--Line 4518:
CREATE TABLE bpd.global_prop_link_class_prop (
id_conception bigint NOT NULL,
    id_global_prop bigint NOT NULL,
    id_class bigint NOT NULL,
    id_class_prop_definition bigint NOT NULL
);

--Line 4533:
CREATE TABLE bpd.prop_enum_val (
id bigint NOT NULL,
    id_prop_enum bigint NOT NULL,
    id_conception bigint NOT NULL,
    val_numeric numeric,
    val_varchar character varying(100),
    id_object_reference bigint DEFAULT '-1'::integer NOT NULL,
    "timestamp" timestamp without time zone NOT NULL,
    sort bigint DEFAULT 1 NOT NULL
);

--Line 4552:
CREATE VIEW bpd.vclass_prop AS
SELECT cp.id,
    cp.id_class,
    cp.timestamp_class,
    cp.on_inherit,
    cp.inheritance,
    cp.id_prop_inherit,
    COALESCE(cp.timestamp_class_inherit, '1990-01-01 00:00:00'::timestamp without time zone) AS timestamp_class_inherit,
    cp.id_prop_type,
    cp.id_data_type,
    cp.name,
    cp."desc",
    cp.sort,
    cp.on_override,
        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
            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
            WHEN 3 THEN (COALESCE(class_val.id, (0)::bigint) > 0)
            WHEN 4 THEN (COALESCE(lv.id_entity_instance, (0)::bigint) > 0)
            ELSE false
        END AS on_val,
    COALESCE(
        CASE cp.id_prop_type
            WHEN 1 THEN
            CASE cp.id_data_type
                WHEN 1 THEN sv.val_varchar
                WHEN 2 THEN (sv.val_int)::character varying
                WHEN 3 THEN (sv.val_numeric)::character varying
                WHEN 4 THEN (sv.val_real)::character varying
                WHEN 5 THEN (sv.val_double)::character varying
                WHEN 6 THEN ((sv.val_money)::numeric)::character varying
                WHEN 7 THEN 'Текст'::character varying
                WHEN 8 THEN 'Изображение'::character varying
                WHEN 9 THEN (sv.val_boolean)::character varying
                WHEN 10 THEN (sv.val_date)::character varying
                WHEN 11 THEN (sv.val_time)::character varying
                WHEN 12 THEN (sv.val_interval)::character varying
                WHEN 13 THEN (sv.val_timestamp)::character varying
                WHEN 14 THEN 'Json'::character varying
                WHEN 15 THEN (sv.val_bigint)::character varying
                ELSE 'н/д'::character varying
            END
            WHEN 2 THEN
            CASE cp.id_data_type
                WHEN 1 THEN pev.val_varchar
                WHEN 3 THEN (pev.val_numeric)::character varying
                ELSE 'н/д'::character varying
            END
            WHEN 3 THEN 'Объект'::character varying
            WHEN 4 THEN 'Ссылка'::character varying
            ELSE 'н/д'::character varying
        END, 'н/д'::character varying) AS string_val,
    ((cp.tableoid)::regclass)::character varying AS tablename,
        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 AS ready,
    cp.id_conception,
    cp.id_class_definition,
    cp.timestamp_class_definition,
    cp.id_prop_definition,
        CASE cp.inheritance
            WHEN true THEN COALESCE(cp_inherit.on_override, false)
            WHEN false THEN cp.on_override
            ELSE NULL::boolean
        END AS on_override_prop_inherit,
    (lgp.id_global_prop IS NOT NULL) AS on_global,
    COALESCE(lgp.id_global_prop, (0)::bigint) AS id_global_prop,
    cp.tag
   FROM (((((((((ONLY bpd.class_prop cp
     LEFT JOIN ONLY bpd.class_prop cp_inherit ON ((cp_inherit.id = cp.id_prop_inherit)))
     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)))
     LEFT JOIN bpd.global_prop_link_class_prop lgp ON ((lgp.id_class_prop_definition = cp.id_prop_definition)))
  ORDER BY cp.sort, cp.name;

--Line 4690:
CREATE FUNCTION bpd.int_cast_vclass_prop_to_cclass_prop(ivclass_prop bpd.vclass_prop) RETURNS bpd.cclass_prop
LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE
    AS $$
DECLARE
 result  "bpd"."cclass_prop"%ROWTYPE;
BEGIN
     result = ivclass_prop;
     RETURN result;
END;
$$;

--Line 4709:
--lost view dependent on CAST (bpd.vclass_prop AS bpd.cclass_prop)
CREATE VIEW bpd.int_class_ext AS
 SELECT cp.id_class AS id,
    array_agg((cp.*)::bpd.cclass_prop ORDER BY cp.sort) AS property_list
   FROM bpd.vclass_prop cp
  GROUP BY cp.id_class;
 
--Line 4723:
--lost view  dependent on VIEW bpd.int_class_ext
CREATE VIEW bpd.vclass_ext AS
  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 cp_list ON ((cp_list.id = c.id)))
     LEFT JOIN bpd.int_class_ready c_ready ON ((c_ready.id = c.id)))
     LEFT JOIN bpd.int_class_path c_path ON ((c_path.id = c.id)));

--Line 5848:
--lost function dependent on VIEW bpd.int_class_ext
CREATE FUNCTION bpd.class_act_ext_by_id(iid bigint) RETURNS SETOF bpd.vclass_ext
LANGUAGE sql STABLE SECURITY DEFINER ROWS 10 PARALLEL SAFE
    SET search_path TO 'bpd'
    AS $$
    SELECT * FROM bpd.vclass_ext WHERE "id" = iid;
$$;


 --Line 89334:
 CREATE CAST (bpd.vclass_prop AS bpd.cclass_prop) WITH FUNCTION bpd.int_cast_vclass_prop_to_cclass_prop(bpd.vclass_prop);

----------------------------------------------

Everything else follows the same principle.
I am ready to add what is needed, I did not want to clutter it up too much.