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