Массовое изменение свойств функций на VOLATILE

DO $$
declare
_rec  record;
_args text;
s text;
begin
 for _rec in (
 --113
SELECT
      n.nspname AS schema
      ,proname AS fname
      --,proargnames AS args
      --,t.typname AS return_type
      --,d.description
      --,pg_get_functiondef(p.oid) as definition
      ,sum(1) over(partition by n.nspname||'.'||proname) as rn
  FROM pg_proc p
  JOIN pg_type t
    ON p.prorettype = t.oid
  LEFT OUTER
  JOIN pg_description d
    ON p.oid = d.objoid
  LEFT OUTER
  JOIN pg_namespace n
    ON n.oid = p.pronamespace
 WHERE n.nspname not in ('pg_catalog','pkg_process','pkg_user_management') and
       provolatile='s'
       and proname not in ('getphotoids','fnc_get_dict_element','f_increment_num')
 ) loop
  SELECT pg_get_function_identity_arguments((_rec.schema||'.'||_rec.fname)::regproc) into _args;
  s:='alter function '||_rec.schema||'.'||_rec.fname||'('||_args||') VOLATILE;';
  raise notice '%',s;
  execute s;
 end loop;
END
$$

alter function pkg_get_cabinet.get_zas_mrg_guest_list(INOUT refcur refcursor, in_comment_id integer) VOLATILE;

postgres стек ошибки с указанием строки

do language plpgsql $$
declare
  l_message_text text;
  l_excp_context text;
begin

.... код

exception
when others then
    GET STACKED DIAGNOSTICS
      l_message_text = MESSAGE_TEXT,
      l_excp_context := PG_EXCEPTION_CONTEXT;  
    raise notice 'message text: >> % <<', l_message_text;
    raise notice 'exception stack: >> % <<', l_excp_context;
 end;
$$

Пример создания джоба в postgres

1) установка pgagent

yum search pgagent
yum install pgagent_96

2) В каталоге /etc/init.d/ создаем файл и прописываем его в автозагрузку

#!/bin/bash
 #
 # /etc/rc.d/init.d/pgagent
 #
 # Manages the pgagent daemon
 #
 # chkconfig: - 65 35
 # description: PgAgent PostgreSQL Job Service
 # processname: pgagent
 . /etc/init.d/functions
 RETVAL=0
 prog="PgAgent"
 start() {
   echo -n $"Starting $prog: "
   daemon "/usr/bin/pgagent_95 -s /var/log/pgagent_95.log hostaddr=your_ip dbname=cuser=postgres password=YYYYYYYY"
   RETVAL=$?
   echo
 }
 stop() {
   echo -n $"Stopping $prog: "
   killproc /usr/bin/pgagent_95
   RETVAL=$?
   echo
 }
 case "$1" in
  start)
   start
   ;;
  stop)
   stop
   ;;
  reload|restart)
   stop
   start
   RETVAL=$?
   ;;
  status)
   status /usr/bin/pgagent_95
   RETVAL=$?
   ;;
  *)
   echo $"Usage: $0 {start|stop|restart|reload|status}"
   exit 1
 esac
 exit $RETVAL

3) Запуск /etc/init.d/pgagent_95 start

4) Создание расписания/запуск функции каждую минуту (расписание аналогично crontab)

DO $$
DECLARE
    jid integer;
    scid integer;
BEGIN
-- Creating a new job
INSERT INTO pgagent.pga_job(
    jobjclid, jobname, jobdesc, jobhostagent, jobenabled
) VALUES (
    1::integer, 'check events emails'::text, ''::text, ''::text, true
) RETURNING jobid INTO jid;

-- Steps
-- Inserting a step (jobid: NULL)
INSERT INTO pgagent.pga_jobstep (
    jstjobid, jstname, jstenabled, jstkind,
    jstconnstr, jstdbname, jstonerror,
    jstcode, jstdesc
) VALUES (
    jid, 'check events emails'::text, true, 's'::character(1),
    ''::text, 'cabinet'::name, 'f'::character(1),
    'begin;
       select msg.fnc_job_check_events_letters();'::text, ''::text
) ;

-- Schedules
-- Inserting a schedule
INSERT INTO pgagent.pga_schedule(
    jscjobid, jscname, jscdesc, jscenabled,
    jscstart, jscend,    jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths
) VALUES (
    jid, 'del'::text, ''::text, true,
    '2017-04-07 16:44:15+05'::timestamp with time zone, '2057-04-07 16:30:30+05'::timestamp with time zone,
    -- Minutes
    ARRAY[true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true]::boolean[],
    -- Hours
    ARRAY[true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true]::boolean[],
    -- Week days
    ARRAY[true, true, true, true, true, true, true]::boolean[],
    -- Month days
    ARRAY[true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true, true]::boolean[],
    -- Months
    ARRAY[true, true, true, true, true, true, true, true, true, true, true, true]::boolean[]
) RETURNING jscid INTO scid;
END
$$;

------------------------
5) 2 функции и тестовая таблица.

CREATE OR REPLACE FUNCTION msg.fnc_job_check_events_letters()
 RETURNS void
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
declare
  v_conn_str text;
  v_conn_name varchar(64) := md5(clock_timestamp()::varchar);
  v_query text;
  v_res numeric;
begin

   select conn_str
     into v_conn_str
     from public.connect_data;
 
     v_query := 'SELECT msg.fnc_exec_check_events_letters()';

  PERFORM  public.dblink_connect(v_conn_name,v_conn_str);
  PERFORM public.dblink_exec(v_conn_name, 'SET datestyle = ISO, DMY;');          
 
  SELECT *
  into v_res
  FROM public.dblink(v_conn_name, v_query) AS p(res numeric);

  end;
$function$


CREATE OR REPLACE FUNCTION msg.fnc_exec_check_events_letters()
 RETURNS numeric
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
declare
begin
 insert into msg.test_jobs(id) values(1);
 return 1;
end;
$function$

create table msg.test_jobs(
date_inserted timestamp NOT NULL DEFAULT 'now'::text::timestamp without time zone,
id numeric);

Примеры работы с комплексными типами (коллекции) пополнение/циклы

CREATE TYPE msg.entity_object AS (
  field_id     numeric,
  field_value  text
);



DO $$
declare
  entity_Before_arr  msg.entity_object[];
  _rec               msg.entity_object%rowtype;
  _record            record;
begin

/*
 --1
 entity_Before_arr := (SELECT ARRAY(SELECT (cf.field_id,cf.field_value)
                                      FROM er.ref_comment_field cf
                                     WHERE cf.comment_id = 986)
                      );
*/
--2
SELECT INTO entity_Before_arr
ARRAY(SELECT (cf.field_id,cf.field_value)
        FROM er.ref_comment_field cf
       WHERE cf.comment_id = 986);
/*
--1
FOREACH _rec IN ARRAY entity_Before_arr
  LOOP
    RAISE NOTICE 'field_id: % - %', _rec.field_id,_rec.field_value;
  END LOOP;
  */
 
 for _record in select unnest(entity_Before_arr) as v loop
  RAISE NOTICE '%-%', (_record.v).field_id, (_record.v).field_value ;
 end loop;
 
END
$$

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

Формируем массив пар полей типа msg.entity_object, для передачи в функции и обработки с помощью SQL.

 SELECT ARRAY(
                                               SELECT cast((cf.field_id,cf.field_value) as msg.entity_object) FROM er.ref_comment_field cf WHERE cf.comment_id = 986
                                               ) as v

Разворачивание полей:

  select (d.val).field_id,
                          (d.val).field_value
                     from(
                          select unnest(entB.v) as val
                            from (
                                  SELECT ARRAY(
                                               SELECT cast((cf.field_id,cf.field_value) as msg.entity_object) FROM er.ref_comment_field cf WHERE cf.comment_id = 986
                                               ) as v
                                 ) entB
                         ) d

Или компактнее

  select (entB.v).field_id,
                                 (entB.v).field_value
                            from (
                                  SELECT unnest(ARRAY(
                                                  SELECT cast((cf.field_id,cf.field_value) as msg.entity_object) FROM er.ref_comment_field cf WHERE cf.comment_id = 986
                                               )) as v
                                 ) entB



===========================================================

Ещё один вид/вариант обработки, собрали массив, передали дальше, проверили что можно его обработать и через SQL и через plPgSQL

DO $$
declare
  entity_Before_arr  msg.entity_object[];
  _rec               msg.entity_object%rowtype;
  _record            record;
begin

--2
SELECT INTO entity_Before_arr
ARRAY(
          SELECT cast((cf.field_id,cf.field_value) as msg.entity_object)
            FROM er.ref_comment_field cf
           WHERE cf.comment_id = 986
         );

 
 for _record in           select (entB.v).field_id,
                                 (entB.v).field_value
                            from (
                                  SELECT unnest(entity_Before_arr) as v
                                 ) entB
 loop
  RAISE NOTICE '%-%', _record.field_id, _record.field_value ;
 end loop;
   
END
$$

Поменять тип полей, с очисткой

DO $$
DECLARE
  _rec record;
  _tag_name varchar(32);
begin
_tag_name := 'tag_678';
 for _rec in
 -----------------------------
SELECT attrelid::regclass as tablename,
       attname,
       (select typname from pg_type where oid=atttypid) as col_type
FROM   pg_attribute
WHERE  --attrelid = 'er.editor_comment_tmp'::regclass AND  
       attnum > 0
and    attname = _tag_name
AND    NOT attisdropped
and    (select typname from pg_type where oid=atttypid)='varchar'
 -----------------------------
 loop
   begin  
 execute 'update '||_rec.tablename||' set '||_tag_name||'=null';
 execute 'alter table '||_rec.tablename||' alter column '||_tag_name||' type numeric using '||_tag_name||'::numeric';
    raise notice 'OK: table - %',_rec.tablename;
   exception
    when others then raise notice 'ERROR: table - %',_rec.tablename;
   end;
 end loop;
END
$$

Обвязка функцией процедуры, возвращающей курсор. С выводом данных.

Есть процедура, возвращающая курсор в виде inout параметра.


BEGIN;
  SELECT * FROM pkg_get_comm.get_comm_with_fields(refcur => 'qwe', in_user=> 111111111,in_tab=> 423,in_sys=> 330,in_comment=> 556);
 
FETCH ALL IN qwe;

Требуется написать функцию которая внутри выполнит процедуру, получит данные и вернет их из функции как из обычной таблицы.

1)
Руками явно достаем запрос из процедуры, выполняем его создав пустую таблицу методом ctas. Эта таблица не содержит данные и используется только для описания типа. Можно было бы создавать не таблицу, а явно тип в бд.

2) Создаем функцию

CREATE OR REPLACE FUNCTION pkg_get_comm.get_comm_with_fields_part(in_user numeric, in_tab numeric, in_sys numeric, in_comment numeric, in_is_debug numeric DEFAULT 0, in_theme numeric DEFAULT NULL::numeric)
 RETURNS SETOF pkg_get_comm.t_type_gcwf
 LANGUAGE plpgsql
 STABLE SECURITY DEFINER
AS $function$
declare
  _cur_name  varchar(64):= md5(clock_timestamp()::varchar);
  _rec       pkg_get_comm.t_type_gcwf%rowtype; 
  _rcur      refcursor;
begin
   SELECT _cur_name
     INTO _rcur
     FROM pkg_get_comm.get_comm_with_fields(_cur_name::REFCURSOR,
                                            in_user    => in_user,
                                            in_tab     => in_tab,
                                            in_sys     => in_sys,
                                            in_comment => in_comment);
  LOOP
    FETCH _rcur INTO _rec;
    EXIT WHEN NOT FOUND;
    RETURN NEXT _rec;
  END LOOP;
end;
$function$

3) Пример использования. можно фильтровать по полям, запрашивать только нужные поля.

select * from pkg_get_comm.get_comm_with_fields_part(in_user=> 111111111,in_tab=> 423,in_sys=> 330,in_comment=> 556)

Работа с курсорами. возвращаемыми функциями, autocommit, явное начало транзакции

Пусть есть процедура возвращающая курсор через INOUT параметр:

drop function p_return_cursor(inout refcur refcursor);

create or replace function p_return_cursor(inout refcur refcursor) 
 returns refcursor -- тут именно refcursor, т.к. он есть в параметрах с типом OUT 
 LANGUAGE plpgsql
as $$
begin
 open refcur for select generate_series(1,10,1) as ID;
end 
$$

Как в postgres в SQL вызвать её и получить набор данных.
1) 
select p_return_cursor('refcur');

результат - '-- refcursor' ничего! 

Произошёл вызов процедуры, был открыт курсор, далее курсор был вернут вызывающей среде (ссылка на курсор - указатель на первую запись) и тут же выполнен autocommit - при этом курсор сразу закрылся и более не существует. Поэтому в вызывающей среде мы ни чего не имеем.

Комментарий: 
Important Note: The cursor remains open until the end of transaction, and since PostgreSQL works in auto-commit mode by default, the cursor is closed immediately after the procedure call, so it is not available to the caller. 

To work with cursors you have to start a transaction (turn auto-commit off).

Для явного старта транзакции можно использовать BEGIN;

BEGIN initiates a transaction block, that is, all statements after a BEGIN command will be executed in a single transaction until an explicit COMMIT or ROLLBACK is given. 

By default (without BEGIN), PostgreSQL executes transactions in "autocommit" mode, that is, each statement is executed in its own transaction and a commit is implicitly performed at the end of the statement (if execution was successful, otherwise a rollback is done).

Поэтому:
1)
begin;
 select p_return_cursor('refcur');

и дальше мы находимся в запущенной транзакции, Postgres ждёт от нас explicit COMMIT or ROLLBACK. :) Тут можно посидеть, подумать, а нужны ли нам данные и если до, то можно извлечь их из открытого курсора.

 fetch all in refcur;

1
2
3
4
5
6
7
8
9

10

Тут же выполняем повторно и уже ни чего не получаем, но не генерируется и ошибка, т.к. просто указать курсора находится в конце.

Можно передвинуть его на первую запись и опять выполнить извлечение.


 MOVE first from refcur;
 fetch all in refcur;

2
3
4
5
6
7
8
9

10

Почему с 2, а не с 1 пока для меня загадка!

Так же явно начать транзакцию можно так:

START TRANSACTION ISOLATION LEVEL READ COMMITTED READ only;

 select p_return_cursor('refcur');

 fetch all in refcur;