Пример создания джоба в 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
$$