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

Загрузка из 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';

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

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');

Блокировки/Висящие сессии, отключение сессии, активные сессии

Блокировки
select
  lock.locktype,
  lock.relation::regclass,
  lock.mode,
  lock.transactionid as tid,
  lock.virtualtransaction as vtid,
  lock.pid,
  lock.granted--, pg_terminate_backend(lock.pid)
from pg_catalog.pg_locks lock
  left join pg_catalog.pg_database db
    on db.oid = lock.database
where (db.datname = 'sandbox' or db.datname is null)
  and not lock.pid = pg_backend_pid()
order by lock.pid;


отключение сессии
select pg_terminate_backend(24736) сюда pid


активные сессии
select * from pg_stat_activity

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

Проход в цикле

DO $$
DECLARE
  _rec record;
  _res boolean;
BEGIN
  for _rec in
  select distinct
  lock.pid
from pg_catalog.pg_locks lock
  left join pg_catalog.pg_database db
    on db.oid = lock.database
where (db.datname = 'sandbox' or db.datname is null)
  and not lock.pid = pg_backend_pid()
order by lock.pid loop
_res := pg_terminate_backend(_rec.pid);
end loop;
END
$$;

№2
SELECT
   pg_terminate_backend(pid) 
FROM
    pg_stat_activity
WHERE
    -- don't kill my own connection!
    pid <> pg_backend_pid()
    -- don't kill the connections to other databases
    AND datname = 'cabinet';

Пример передачи в функцию 2-х коллекций и возврат 2-х курсоров

CREATE OR REPLACE FUNCTION study.test_collection_param(
                                                       INOUT refcur1    refcursor,
                                                       INOUT refcur2    refcursor,
                                                             in_coll_n  int[],
                                                             in_coll_s  varchar(50)[] default '{}'::varchar(50)[]
                                                      )
 RETURNS record
 LANGUAGE plpgsql
 STABLE SECURITY DEFINER
AS $function$
begin
open refcur1 for
         select d.id_dict,
                sum(1)   as cnt,
                sum(val) as val
           from study.dat d
          where d.id_dict = ANY(in_coll_n)
          group by d.id_dict;
    open refcur2 for
         select unnest(in_coll_s) as COL_NAME;
end;
$function$

commit;

begin;
 select study.test_collection_param('rc1','rc2',ARRAY[1,2,3],ARRAY['x1','y2',' ','z3']); 

FETCH ALL IN rc1;
1 17 916
2 17 921
3 17 905

FETCH ALL IN rc2;
COL_NAME
x1
y2

z3

Передача массива (коллекции чисел) на вход функции, использование в SQL

create table dat(
id_dict integer not null constraint fk_dat_pok  references d_dict(id),
id_year integer not null constraint fk_dat_year references d_years(id),
val     numeric,
constraint uk_data unique (id_dict,id_year)
);

CREATE OR REPLACE FUNCTION study.test_collection_param(
                                                       INOUT refcur refcursor,
                                                       in_coll_n int[]
                                                      )
 RETURNS refcursor
 LANGUAGE plpgsql
 STABLE SECURITY DEFINER
AS $function$
begin
open refcur for
         select d.id_dict,
                sum(1)   as cnt,
                sum(val) as val
           from study.dat d
          where d.id_dict = ANY(in_coll_n)
          group by d.id_dict;
end;
$function$

Пример вызова

begin;
 select study.test_collection_param('rc',ARRAY[1,2,3]);

FETCH ALL IN rc;

Сравнение планов, при использовании коллекции в подзапросе:

 Вариант 1.

        explain analyze
         select d.id_dict,d.val
           from study.dat d
          where d.id_dict = ANY(ARRAY[1,2,3])

План.

Bitmap Heap Scan on dat d  (cost=13.23..23.93 rows=51 width=8) (actual time=0.018..0.033 rows=51 loops=1)
  Recheck Cond: (id_dict = ANY ('{1,2,3}'::integer[]))
  Heap Blocks: exact=9
  ->  Bitmap Index Scan on uk_data  (cost=0.00..13.21 rows=51 width=0) (actual time=0.014..0.014 rows=51 loops=1)
        Index Cond: (id_dict = ANY ('{1,2,3}'::integer[]))
Planning time: 0.050 ms
Execution time: 0.052 ms

 Execution time: 0.088 ms
 Execution time: 0.061 ms
 Execution time: 0.056 ms

 Вариант 2.

        explain analyze
         select d.id_dict,d.val
           from study.dat d
         where d.id_dict in (select unnest(array[1,2,3]))

План.

Hash Semi Join  (cost=2.76..53.13 rows=1700 width=8) (actual time=0.018..0.244 rows=51 loops=1)
  Hash Cond: (d.id_dict = (unnest('{1,2,3}'::integer[])))
  ->  Seq Scan on dat d  (cost=0.00..27.00 rows=1700 width=8) (actual time=0.006..0.103 rows=1700 loops=1)
  ->  Hash  (cost=1.51..1.51 rows=100 width=4) (actual time=0.007..0.007 rows=3 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  Result  (cost=0.00..0.51 rows=100 width=0) (actual time=0.004..0.005 rows=3 loops=1)
Planning time: 0.096 ms
Execution time: 0.261 ms

 Execution time: 0.296 ms
 Execution time: 0.266 ms
 Execution time: 0.270 ms