Логирование удаления объектов в postgres

select inet_client_addr()

create table oz.important_table(id numeric);
insert into oz.important_table values(1);

select * from oz.important_table

create view oz.v_test as select * from oz.important_table;

drop view oz.v_test;

select * from oz.v_test

CREATE OR REPLACE FUNCTION public.trg_log_drop()
 RETURNS event_trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
  obj record;
BEGIN
  FOR obj in SELECT * FROM pg_event_trigger_dropped_objects() LOOP
    IF obj.object_type in ('table','view') THEN
      RAISE NOTICE 'TABLE % DROPPED by transaction % (pre-commit)',
                   obj.object_identity, txid_current();
      INSERT INTO public.droplog (tablename, dropxid,user_ip)
             VALUES (obj.object_identity, txid_current(),inet_client_addr());
    END IF;
  END LOOP;
END;
$function$

CREATE EVENT TRIGGER table_drop_logger
  ON sql_drop
  WHEN TAG IN ('DROP TABLE','DROP VIEW')
  EXECUTE PROCEDURE public.trg_log_drop()

CREATE TABLE public.droplog (
  ts        timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  tablename text NOT NULL,
  dropxid   bigint,
  user_ip   text
);

select * from public.droplog

drop table oz.important_table;

Извлечение данных из 2-х курсоров, и возврат курсора

CREATE TABLE pkg_get_cabinet_element.tmp_get_button_tbl (
id numeric NULL,
"name" varchar(500) NULL,
is_edit int4 NULL,
param varchar NULL,
"action" varchar NULL,
dependences_list json NULL,
ico varchar(20) NULL,
action2 varchar(2000) NULL,
button_param varchar(4000) NULL,
identifier varchar(100) NULL
);

CREATE OR REPLACE FUNCTION pkg_get_cabinet_element.get_buttons_for_grades(in_user numeric, in_comment numeric, in_org numeric DEFAULT NULL::numeric)
 RETURNS SETOF pkg_get_cabinet_element.tmp_get_button_tbl
 LANGUAGE plpgsql
 STABLE SECURITY DEFINER
AS $function$
declare
  _cur_name1  varchar(64):= md5(clock_timestamp()::varchar);
  _cur_name2  varchar(64):= md5(clock_timestamp()::varchar);
  _rec       pkg_get_cabinet_element.tmp_get_button_tbl%rowtype;
  _rcur      refcursor;
begin
   SELECT _cur_name1
     INTO _rcur
     FROM pkg_get_cabinet_element.get_button(refcur     => _cur_name1::REFCURSOR, in_user => in_user, in_tab => 963, in_sys => 450, in_org => in_org, in_comment => in_comment);
  LOOP
    FETCH _rcur INTO _rec;
    EXIT WHEN NOT FOUND;
    RETURN NEXT _rec;
    raise notice '%','-1-';
  END LOOP;
   SELECT _cur_name2
     INTO _rcur
     FROM pkg_get_cabinet_element.get_button(refcur     => _cur_name2::REFCURSOR, in_user => in_user, in_tab => 1563, in_sys => 490, in_org => in_org, in_comment => in_comment);
  LOOP
    FETCH _rcur INTO _rec;
    EXIT WHEN NOT FOUND;
    RETURN NEXT _rec;
    raise notice '%','-2-';
  END LOOP;
end;
$function$

-----------

select * from pkg_get_cabinet_element.get_buttons_for_grades(in_user=> 71389407,in_org=> 1001,in_comment=> 3869)

id   |name                 |is_edit |param |action |dependences_list                                                                                     |ico          |action2 |button_param |identifier |
-----|---------------------|--------|------|-------|-----------------------------------------------------------------------------------------------------|-------------|--------|-------------|-----------|
2306 |Изменить специалиста |1       |      |save   |{ "sourceDependency": {"0": {"field_id": 679, "field_code": "tag_679", "ids_list":"10", "val":""}} } |icon_ui_redo |edit    |             |5.02.2     |
2305 |Принять в работу     |1       |      |save   |{ "sourceDependency": {"0": {"field_id": 679, "field_code": "tag_679", "ids_list":"3", "val":""}} }  |             |        |             |5.02.1     |

DO $$
declare
 cur refcursor := 'qwe';
begin
 open cur for
  select * from pkg_get_cabinet_element.get_buttons_for_grades(in_user=> 71389407,in_org=> 1001,in_comment=> 3869);
END
$$
   
fetch all in qwe;

id   |name                 |is_edit |param |action |dependences_list                                                                                     |ico          |action2 |button_param |identifier |
-----|---------------------|--------|------|-------|-----------------------------------------------------------------------------------------------------|-------------|--------|-------------|-----------|
2306 |Изменить специалиста |1       |      |save   |{ "sourceDependency": {"0": {"field_id": 679, "field_code": "tag_679", "ids_list":"10", "val":""}} } |icon_ui_redo |edit    |             |5.02.2     |
2305 |Принять в работу     |1       |      |save   |{ "sourceDependency": {"0": {"field_id": 679, "field_code": "tag_679", "ids_list":"3", "val":""}} }  |             |        |             |5.02.1     |