Postgres ideas

среда, 2 августа 2017 г.

Извлечение данных из 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 |

четверг, 20 июля 2017 г.

Массовое изменение свойств функций на 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;

четверг, 6 июля 2017 г.

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;
$$

вторник, 20 июня 2017 г.

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

четверг, 1 июня 2017 г.

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

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
$$

среда, 17 мая 2017 г.

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

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
$$

воскресенье, 14 мая 2017 г.

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

Есть процедура, возвращающая курсор в виде 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)

среда, 2 августа 2017 г.

четверг, 20 июля 2017 г.

четверг, 6 июля 2017 г.

вторник, 20 июня 2017 г.

четверг, 1 июня 2017 г.

среда, 17 мая 2017 г.

воскресенье, 14 мая 2017 г.