Postgres ideas

вторник, 13 февраля 2018 г.

Иерархический запрос в postgres Аналог order siblings by

WITH RECURSIVE temp1(hierarchy,id,parent_id,industry_tree_name,order_by,lvl) AS
(
select ARRAY[it.industry_tree_id] AS hierarchy,
it.industry_tree_id as id,
it.industry_tree_parent_id as parent_id,
it.industry_tree_name,
it.order_by,
it.lvl
from industry_tree it
where it.industry_version_id = 11
and it.active_from <= current_timestamp
and it.active_to > current_timestamp
union
select temp1.hierarchy || t2.industry_tree_id AS hierarchy,
t2.industry_tree_id as id,
t2.industry_tree_parent_id as parent_id,
t2.industry_tree_name,
t2.order_by,
temp1.lvl+1 as lvl
from industry_tree t2
INNER JOIN temp1 on (temp1.id = T2.industry_tree_parent_id)
and t2.active_from <= current_timestamp
and t2.active_to > current_timestamp
)
select *
from temp1
ORDER BY hierarchy;

Взято с stackoverflow.com

понедельник, 15 января 2018 г.

Вакуум, статистика

-- примерное количестов записей в таблице
SELECT relname,reltuples AS approximate_row_count FROM pg_class where relowner=22268 and relkind='r' order by 2 desc

-- информация по вакууму и статистике
select 'vacuum analyze '||schemaname||'.'||relname||';' as tbl,last_vacuum,last_analyze,last_autovacuum,autovacuum_count,last_autoanalyze,
(
SELECT reltuples AS approximate_row_count
FROM pg_class
where relowner=22268 and relkind='r' and relname=att.relname
) as apx_row_nums
from pg_stat_all_tables att
where att.schemaname not in ('pg_catalog','pg_toast','prm_analytics','prm_ctp','public',
'prm_log','auth','prm_analytics_rep','information_schema','prm_root')
--
/*
and
autovacuum_count=0 and
last_autovacuum is null and
last_autoanalyze is null and
last_vacuum is null
--
*/
order by att.autovacuum_count desc

commit;

Статистика по запросам

select pss.*
from pg_stat_statements pss
where pss.query not like 'FETCH ALL IN cursor%' and
pss.query not like 'CLOSE cursor%' and
pss.query not like '%privilege%' and
pss.query not like 'SET datestyle%'
order by pss.total_time desc

четверг, 28 декабря 2017 г.

Мониторинг использования, статистика

Взято с pgtune

select * from pg_stat_user_tables q order by q.seq_scan desc

select * from pg_statio_user_tables t order by t.heap_blks_read desc

Расширение CREATE EXTENSION pg_buffercache;

использование буферов объектами (таблицами, индексами, прочим):

SELECT c.relname, count(*) AS buffers
FROM pg_buffercache b INNER JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid) AND
b.reldatabase IN (0, (SELECT oid FROM pg_database WHERE datname = current_database()))
GROUP BY c.relname
ORDER BY 2 DESC
LIMIT 10;

relname |buffers |
--------------------|--------|
sl_user_access_role |35119 |
sl_user_access |18547 |
t_users |17817 |
msg_content |14132 |
sl_report_data |13379 |

объекты (таблицы и индексы) в кэше:

 SELECT c.relname, count(*) AS buffers,usagecount
 FROM pg_class c
 INNER JOIN pg_buffercache b
 ON b.relfilenode = c.relfilenode
 INNER JOIN pg_database d
 ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.relname,usagecount
ORDER BY c.relname,usagecount;

relname |buffers |usagecount |
---------------------------------|--------|-----------|
sl_user_access_role |35119 |5 |
sl_user_access |18546 |5 |
t_users |17817 |5 |
msg_content |14147 |5 |
sl_report_data |13396 |5 |
pk_sl_f_cons_rep |10942 |5 |
pk_sl_f_form_rep |10520 |5 |

Это запрос показывает какой процент общего буфера используют обьекты (таблицы и индексы) и на сколько процентов объекты находятся в самом кэше (буфере):

SELECT
 c.relname,
 pg_size_pretty(count(*) * 8192) as buffered,
 round(100.0 * count(*) /
 (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1)
 AS buffers_percent,
 round(100.0 * count(*) * 8192 / pg_table_size(c.oid),1)
 AS percent_of_relation
FROM pg_class c
 INNER JOIN pg_buffercache b
 ON b.relfilenode = c.relfilenode
 INNER JOIN pg_database d
 ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.oid,c.relname
ORDER BY 3 DESC
LIMIT 20;

relname |buffered |buffers_percent |percent_of_relation |
---------------------------|---------|----------------|--------------------|
sl_user_access_role |274 MB |6.7 |100.0 |
sl_user_access |145 MB |3.5 |100.0 |
t_users |139 MB |3.4 |100.0 |
msg_content |111 MB |2.7 |100.0 |
sl_report_data |105 MB |2.6 |99.9 |
pk_sl_f_cons_rep |85 MB |2.1 |98.3 |
pk_sl_f_form_rep |82 MB |2.0 |98.5 |
mv_budgets_last_sum |82 MB |2.0 |100.0 |
sl_f_form_rep |40 MB |1.0 |99.9 |
sl_report_requisites |36 MB |0.9 |20.2 |

sl_f_cons_rep |39 MB |0.9 |99.9 |

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

select proname,prosrc from pg_proc where proname= your_function_name;

четверг, 30 ноября 2017 г.

Интервал выполнения и sleep

do $$
DECLARE
t NUMERIC;
BEGIN
select EXTRACT(EPOCH FROM timeofday()::TIMESTAMP) into t;
raise notice 't=%',t;

perform pg_sleep(2);

select EXTRACT(EPOCH FROM timeofday()::TIMESTAMP) into t;
raise notice 't=%',t;

select EXTRACT(EPOCH FROM timeofday()::TIMESTAMP) into t;
raise notice 't=%',t;

end $$

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

00000: t=1512065827.6849

00000: t=1512065829.6977

00000: t=1512065829.69793

среда, 11 октября 2017 г.

BAT file for backup postgres schema

@echo off
set PGPASSWORD=prm_salary
set PGUSER=prm_salary
set PGDATABASE=db_ris_mkrpk
set PGBIN="C:\Program Files\PostgreSQL\9.6\bin\"
set t=%date%_%time%
set d=%t:~10,4%%t:~7,2%%t:~4,2%_%t:~15,2%%t:~18,2%%t:~21,2%
set BACKUP_FILE="C:\dumps\prm_salary_%d%.dmp"
set FILELOG="C:\dumps\prm_salary_%d%.log"
(
echo Backup start %date% %time% file %BACKUP_FILE%
%PGBIN%"pg_dump" --schema=prm_salary --format=c --file=%BACKUP_FILE%
echo End of backup %date% %time%
)>> %FILELOG% 2>&1
forfiles /p "C:\dumps" /s /m *.log /D -5 /C "cmd /c del @path"
forfiles /p "C:\dumps" /s /m *.dmp /D -5 /C "cmd /c del @path"

среда, 20 сентября 2017 г.

Блокировки

-- запросы онлайн
SELECT * FROM pg_stat_activity order by client_addr;

-- блокировки по пидам
SELECT locktype, relation::regclass, mode, transactionid AS tid,
virtualtransaction AS vtid, pid, granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid = l.database WHERE (db.datname = 'sandbox' OR db.datname IS NULL)
AND NOT pid = pg_backend_pid()

--3
SELECT locktype, relation::regclass,mode, transactionid AS tid,
virtualtransaction AS vtid,pid, granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid=l.database WHERE (db.datname='cabinet' OR db.datname IS NULL)
AND NOT pid = pg_backend_pid();

SELECT bl.pid AS blocked_pid,
a.usename AS blocked_user,
kl.pid AS blocking_pid,
ka.usename AS blocking_user,
a.query AS blocked_statement
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid
JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted

select pid,
usename,
pg_blocking_pids(pid) as blocked_by,
query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0

SELECT
COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,
blockeda.query as blocked_query, blockedl.mode as blocked_mode,
blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
( (blockingl.transactionid=blockedl.transactionid) OR
(blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
AND blockinga.datname = current_database()

select t.relname,l.locktype,page,virtualtransaction,pid,mode,granted from pg_locks l, pg_stat_all_tables t where l.relation=t.relid order by relation asc;

SELECT blockeda.pid AS blocked_pid, blockeda.query as blocked_query,
blockinga.pid AS blocking_pid, blockinga.query as blocking_query
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(blockingl.transactionid=blockedl.transactionid
AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted AND blockinga.datname='cabinet';

понедельник, 4 сентября 2017 г.

Размер таблиц в postgres

см. прочие поля в таблице.

SELECT oid,
table_schema,
table_name,
total_bytes,
pg_size_pretty(total_bytes) AS humna_size
FROM (
SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r'
) a
) a
-- where round(total_bytes/1024/1024) > 100 -- только если размер больше 100Мб
order by total_bytes desc

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

Логирование удаления объектов в 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 августа 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)

пятница, 7 апреля 2017 г.

Работа с курсорами. возвращаемыми функциями, 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;

Пример функции возвращающей набор данных, параметризованное представление

create or replace function f_get_data()
RETURNS TABLE(id integer)
LANGUAGE plpgsql
as $$
declare
begin
return query
select generate_series(1,10,1) as ID;
end
$$

Изначально известен возвращаемый тип данных TABLE(id integer)
по всей видимости в момент выполнения запускается запрос и возвращается результат.

Примеры вызова: можно использовать как таблицу в простом SQL.

select f_get_data()

select * from f_get_data() t where t.id>=5

Если запрос в функции динамический, то можно использовать:

return query execute 'select generate_series(1,10,1) as ID';

в том числе начиная с 8.4 можно использовать using

RETURN QUERY EXECUTE sql USING var1, var2;

четверг, 6 апреля 2017 г.

Загрузка из csv в таблицу

Создаем таблицу, копируем файл в ОС

create table cabinet.buff_kpgz(
id integer,
date_time_create character varying(128),
date_time_chnage character varying(128),
code_kpgz character varying(128),
name character varying(1000),
prnt_kpgz character varying(1000),
s_desc character varying(4000),
okpd character varying(128),
okpd2 character varying(128),
code_mer character varying(128),
is_need_be_ref character varying(128),
is_stand_position character varying(128),
is_gov_expert character varying(128),
to_repair character varying(128),
obj_type character varying(128),
project character varying(128)
);

select * from cabinet.buff_kpgz

в Dbeaver
COPY cabinet.buff_kpgz FROM '/home/kpgz.csv' DELIMITER ';' CSV HEADER encoding 'windows-1251';

среда, 5 апреля 2017 г.

привязка сиквенса к полю таблицы

CREATE SEQUENCE cabinet.s_ref_theme_process
INCREMENT BY 2
MINVALUE 2
NO MAXVALUE
START WITH 2;

alter table cabinet.ref_theme_process alter column id set default nextval('cabinet.s_ref_theme_process');

вторник, 13 февраля 2018 г.

понедельник, 15 января 2018 г.

четверг, 28 декабря 2017 г.

четверг, 30 ноября 2017 г.

среда, 11 октября 2017 г.

среда, 20 сентября 2017 г.

понедельник, 4 сентября 2017 г.

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

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

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

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

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

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

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

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

пятница, 7 апреля 2017 г.

четверг, 6 апреля 2017 г.

среда, 5 апреля 2017 г.