​​В любом языке программирования есть такое понятие, как “зарезервированные слова”.
В SQL ключевые слова делятся на зарезервированные и незарезервированные.
Согласно стандарту, действительно ключевыми словами являются только зарезервированные слова, которые не могут быть идентификаторами (названиями сущностей: таблиц, столбцов, псевдонимов и т.д.). Незарезервированные ключевые слова имеют особое значение только в определённых контекстах и могут быть идентификаторами в других. Большинство незарезервированных ключевых слов представляют имена встроенных таблиц и функций, определённых в SQL. Незарезервированные ключевые слова используются для того, чтобы показать, что эти слова имеют некоторое предопределённое значение в отдельных контекстах.

Список ключевых слов для PostgreSQL 16 версии по ссылке: https://postgrespro.ru/docs/postgresql/16/sql-keywords-appendix#KEYWORDS-TABLE

​​PG_TERMINATE_BACKEND - функция, которая завершает сеанс, который обслуживается процессом с заданным PID. Это действие разрешается и ролям, являющимся членами роли, процесс которой прерывается, и ролям с правами роли pg_signal_backend. Однако только суперпользователям разрешено прерывать обслуживающие процессы других суперпользователей.
Синтаксис:

pg_terminate_backend ( pid integer, timeout bigint DEFAULT 0 ) → boolean

Если параметр timeout не задан или равен нулю, данная функция возвращает true независимо от того, действительно ли завершается процесс. То есть результат показывает только, что сигнал был отправлен успешно. Если параметр timeout задан (в миллисекундах) и больше нуля, функция ожидает, пока процесс не будет фактически завершён или пока не пройдёт заданное время. Если процесс завершён, функция возвращает true. В случае тайм-аута выдаётся предупреждение и возвращается false.

К примеру, кто-то запустил запрос, который начинает “подвешивать” систему и его нужно прервать:

select *
from ticket_flights tf, ticket_flights tf2, ticket_flights tf3
order by random() ;

Что бы узнать, какие процессы в данный момент выполняются, обратимся к метаданным pg_stat_activity:

select pid, usename, client_addr, backend_start, query
from pg_stat_activity 
where state = 'active';

pid |usename |client_addr|backend_start                |query                                                                                               |
----+--------+-----------+-----------------------------+----------------------------------------------------------------------------------------------------+
1032|postgres|127.0.0.1  |2024-05-28 16:18:04.887108+03|select *¶from ticket_flights tf, ticket_flights tf2, ticket_flights tf3¶order by random()           |
6516|postgres|127.0.0.1  |2024-05-28 14:07:24.946435+03|select pid, usename, client_addr, backend_start, query¶from pg_stat_activity ¶where state = 'active'|

Определяем PID равный 1032 и через PG_TERMINATE_BACKEND обрываем выполнение:

select pg_terminate_backend(1032, 1000);

pg_terminate_backend|
--------------------+
false               |

PID 1032 не относится к обслуживающему процессу PostgreSQL

Смотрим процессы еще раз:

select pid, usename, client_addr, backend_start, query
from pg_stat_activity 
where state = 'active';

pid  |usename |client_addr|backend_start                |query                                                                                               |
-----+--------+-----------+-----------------------------+----------------------------------------------------------------------------------------------------+
12632|postgres|127.0.0.1  |2024-05-28 16:36:02.421136+03|select *¶from ticket_flights tf, ticket_flights tf2, ticket_flights tf3¶order by random()           |
13612|postgres|127.0.0.1  |2024-05-28 16:36:02.333122+03|select pid, usename, client_addr, backend_start, query¶from pg_stat_activity ¶where state = 'active'|

Видим, что PID изменился и указываем его:

select pg_terminate_backend(12632, 1000);

pg_terminate_backend|
--------------------+
true                |

Функция PG_TERMINATE_BACKEND прерывает обслуживающий процесс, а не сам процесс и именно поэтому в данном случае приходится выполнять команду два раза.

​​PG_CANCEL_BACKEND - функция, которая отменяет текущий запрос в сеансе, который обслуживается процессом с заданным PID. Это действие разрешается и ролям, являющимся членами роли, запрос которой отменяется, и ролям с правами роли pg_signal_backend. Однако только суперпользователям разрешено отменять запросы других суперпользователей.
Синтаксис:

pg_cancel_backend ( pid integer ) → boolean

По аналогии с функцией PG_TERMINATE_BACKEND запустим запрос, который подвешивает систему:

select *
from ticket_flights tf, ticket_flights tf2, ticket_flights tf3
order by random();

Через pg_stat_activity смотрим активные запросы:

select pid, usename, client_addr, backend_start, query
from pg_stat_activity 
where state = 'active';

pid  |usename |client_addr|backend_start                |query                                                                                               |
-----+--------+-----------+-----------------------------+----------------------------------------------------------------------------------------------------+
 5464|postgres|127.0.0.1  |2024-05-28 16:38:11.959398+03|select *¶from ticket_flights tf, ticket_flights tf2, ticket_flights tf3¶order by random()           |
11384|postgres|127.0.0.1  |2024-05-28 16:38:11.897447+03|select pid, usename, client_addr, backend_start, query¶from pg_stat_activity ¶where state = 'active'|

Передаем в PG_CANCEL_BACKEND нужный PID:

select pg_cancel_backend(5464);

pg_cancel_backend|
-----------------+
true             |

Проверяем, действительно ли был отменен запрос:

select pid, usename, client_addr, backend_start, query
from pg_stat_activity 
where state = 'active';

pid  |usename |client_addr|backend_start                |query                                                                                               |
-----+--------+-----------+-----------------------------+----------------------------------------------------------------------------------------------------+
11384|postgres|127.0.0.1  |2024-05-28 16:38:11.897447+03|select pid, usename, client_addr, backend_start, query¶from pg_stat_activity ¶where state = 'active'|

Отличие PG_CANCEL_BACKEND от PG_TERMINATE_BACKEND заключается в том, что первая функция “просит” запрос прекратить выполнения, а вторая принудительно завершает процессы.

30 мая 2024 года Docker Hub перестал работать на территории России.

Естественно на Хабре уже есть множество статей, как обойти данную проблему.

​​%TYPE - наследование типов данных в процедурном языке PL/pgSQL.
Синтаксис:

переменная%TYPE

Конструкция %TYPE предоставляет тип данных переменной или столбца таблицы. Её можно использовать для объявления переменных, содержащих значения из базы данных.

Используя %TYPE, не нужно знать тип данных структуры, на которую вы ссылаетесь. И самое главное, если в будущем тип данных изменится, то вам может не понадобиться изменять определение функции.

Использование %TYPE особенно полезно в полиморфных функциях, поскольку типы данных, необходимые для внутренних переменных, могут меняться от одного вызова к другому. Соответствующие переменные могут быть созданы с применением %TYPE к аргументам и возвращаемому значению функции.

Пример:

Создадим таблиц с товарами:

create table product (
 product_id serial primary key,
 product_name varchar(100) not null,
 qty int not null,
 cost_per_one numeric not null);

Внесем в нее какие-то данные:

insert into product (product_name, qty, cost_per_one)
values ('пылесос', 1, 25000), ('чайник', 10, 2000), ('утюг', 7, 8600);

select * from product;

product_id|product_name|qty|cost_per_one|
----------+------------+---+------------+
         1|пылесос     |  1|       25000|
         2|чайник      | 10|        2000|
         3|утюг        |  7|        8600|

Напишем функцию, которая будет считать общую стоимость товаров за вычетом НДС 20%, входные параметры естественно указываем в том же типе данных, что и атрибуты таблицы:

create or replace function foo(p_qty int, p_cost numeric) 
returns numeric as $$
begin  
 return round(p_qty * p_cost / 1.2, 2);
end;
$$ language plpgsql;

select *, foo(qty, cost_per_one)
from product;

product_id|product_name|qty|cost_per_one|foo     |
----------+------------+---+------------+--------+
         1|пылесос     |  1|       25000|20833.33|
         2|чайник      | 10|        2000|16666.67|
         3|утюг        |  7|        8600|50166.67|

Произошло изменение в поставляемых товарах и количество может быть дробным, приходится менять тип данных:

alter table product alter column qty type numeric;

Пробуем запустить функцию:

select *, foo(qty, cost_per_one)
from product;

SQL Error [42883]: ОШИБКА: функция foo(numeric, numeric) не существует

Так как произошло изменение типа данных, то функция “сломалась”, что бы этого избежать, пересоздадим функцию используя %TYPE:

create or replace function foo(
 p_qty product.qty % type, 
 p_cost product.cost_per_one % type) 
returns numeric as $$
begin  
 return round(p_qty * p_cost / 1.2, 2);
end;
$$ language plpgsql;

select *, foo(qty, cost_per_one)
from product;

product_id|product_name|qty|cost_per_one|foo     |
----------+------------+---+------------+--------+
         1|пылесос     |  1|       25000|20833.33|
         2|чайник      | 10|        2000|16666.67|
         3|утюг        |  7|        8600|50166.67|

Изменим тип данных на integer:

alter table product alter column qty type integer;

Функция все равно продолжает работать:

select *, foo(qty, cost_per_one)
from product;

product_id|product_name|qty|cost_per_one|foo     |
----------+------------+---+------------+--------+
         1|пылесос     |  1|       25000|20833.33|
         2|чайник      | 10|        2000|16666.67|
         3|утюг        |  7|        8600|50166.67|

03.06.2024 Docker Hub разблокирован в России.

В любом случае данная ситуация оказалась “полезной” для многих компаний, проверка на наличие зеркал, резервных образов и так далее не прошла для многих “бесследно”.

​​Прилетел вопрос: можно ли передать таблицу целиком, как входной аргумент функции?

Ответ - да.

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

create or replace function rows_count(table_name regclass) returns int as $$
declare rows_cnt int;
begin
 execute 'select count(*)::int from ' || table_name into rows_cnt;
 return rows_cnt;
end;
$$ language plpgsql;

select rows_count('payment');

rows_count|
----------+
     16049|

OID - уникальный идентификатор объекта, используется внутри PostgreSQL в качестве первичного ключа различных системных таблиц.

REGCLASS - символическое представление числового кода системных таблиц, условно, представляет OID в символьном виде.

Если в разных схемах будет несколько таблиц с одинаковым названием, то автоматически цепляется таблица либо из текущей схемы, либо из схемы по умолчанию, в противном случае нужно явно передавать кроме таблицы еще и указание на схему, где находится таблица.

Так как подставить значение переменной, как наименование таблицы, напрямую в запрос нельзя, то используем оператора EXECUTE, который позволяет сформировать динамичный SQL запрос.

Так как функция COUNT возвращает значение в типе данных bigint, но понимая, что такого количества строк в таблицах точно не будет, приводим результат к типу данных int.

​​Переменная составного типа называется переменной-кортежем (или переменной типа кортежа). Значением такой переменной может быть целый кортеж, полученный в результате выполнения запроса SELECT или FOR, при условии, что набор столбцов запроса соответствует заявленному типу переменной. Доступ к отдельным полям значения кортежа осуществляется как обычно, через точку.
Синтаксис:

имя имя_таблицы%ROWTYPE;
имя имя_составного_типа;

Переменная-кортеж может быть объявлена с таким же типом, как и строка в существующей таблице или представлении, используя нотацию имя_таблицы%ROWTYPE; или с именем составного типа. (Поскольку каждая таблица имеет соответствующий составной тип с таким же именем, то на самом деле в PostgreSQL не имеет значения, пишете ли вы %ROWTYPE или нет. Но использование %ROWTYPE более переносимо.)

Параметры функции могут быть составного типа (строки таблицы). В этом случае соответствующий идентификатор $n будет переменной-кортежем, поля которой можно выбирать, например $1.user_id.

За исходные данные возьмем таблицы платежей и пользователей из базы dvd-rental. Создадим функцию, которая на вход принимает кортеж таблицы payment и результатом возвращает фамилию и имя пользователя, совершившего платеж:

create or replace function foo (t1_row payment) returns text as $$
declare
 t2_row customer%rowtype;
begin
 select * into t2_row
 from customer
 where customer_id = t1_row.customer_id;
 return concat(t2_row.last_name, ' ', t2_row.first_name);
end;
$$ language plpgsql;

Входным аргументом делаем прямую запись, что переменная равна структуре таблицы payment, в блоке DECLARE для переменной указываем, что она равна структуре таблицы customer через %ROWTYPE.

Запустим функцию и проверим результат:

select foo(p.*)
from payment p
where payment_id = 100;

foo          |
-------------+
JONES BARBARA|

Обратите внимание, что в функцию нужно передать в явном виде столбцы какой таблицы нужно принимать, то есть указание просто * работать не будет, нужно передать именно payment.*.

​​Задача на подумать:

Нужно написать алгоритм, который позволит сравнить две строки одинаковой длины на соответствие символов, которые могут быть расположены в разной последовательности.
К примеру, даны две строки: ‘привет мир’ и ‘ртпр ивеим’. Эти две строки имеют одинаковые символы, хоть и в разном порядке и здесь получаем истину.

Логику можно написать через простой SELECT с ручной подстановкой значений или можно написать FUNCTION.

Завтра выложу свои варианты решений, что бы заранее не спойлерить ;)

​​Как всегда, SQL позволяет решить данную задачу разными способами.
Рассмотрим вариант с созданием функции:

Создаем тело функции с двумя входными аргументами, функция возвращает булев тип:

create or replace function foo (text, text) returns boolean as $$
begin

end;
$$ language plpgsql;

В блоке DECLARE объявим две переменные в виде массивов, в которые элементами добавим символы из строк:

create or replace function foo (text, text) returns boolean as $$
declare 
 some_arr1 text[];
 some_arr2 text[];
begin

end;
$$ language plpgsql;

На всякий случай сделаем проверку, что количество символов в строках равно:

create or replace function foo (text, text) returns boolean as $$
declare 
 some_arr1 text[];
 some_arr2 text[];
begin
 if char_length($1) = char_length($2) then
 
 else 
  raise exception 'кол-во символов в строках разное';
 end if; 
end;
$$ language plpgsql;

Добавляем цикл, где через SUBSTRING выдергиваем по 1 символу из строк и через ARRAY_APPEND добавляем эти символы поэлементно в переменные:

create or replace function foo (text, text) returns boolean as $$
declare 
 some_arr1 text[];
 some_arr2 text[];
begin
 if char_length($1) = char_length($2) then
  for i in 1..char_length($1)
  loop
   some_arr1 = array_append(some_arr1, substring($1, i, 1));
   some_arr2 = array_append(some_arr2, substring($2, i, 1));
  end loop;
 else 
  raise exception 'кол-во символов в строках разное';
 end if; 
end;
$$ language plpgsql;

Теперь добавляем условие IF в котором через UNNEST раскрываем массивы, сортируем их и сравниваем два набора значений:

create or replace function foo (text, text) returns boolean as $$
declare 
 some_arr1 text[];
 some_arr2 text[];
begin
 if char_length($1) = char_length($2) then
  for i in 1..char_length($1)
  loop
   some_arr1 = array_append(some_arr1, substring($1, i, 1));
   some_arr2 = array_append(some_arr2, substring($2, i, 1));
  end loop;
 else 
  raise exception 'кол-во символов в строках разное';
 end if; 
 if (select (array(select unnest(some_arr1) order by 1))) = (select (array(select unnest(some_arr2) order by 1))) then 
  return true;
 else 
  return false;
 end if; 
end;
$$ language plpgsql;

Проверяем результат:

select foo('привет мир', 'ртпр ивеим');

foo |
----+
true|

select foo('привет мир', 'ртбб ивеим');

foo  |
-----+
false|

​​Второй вариант решения задачи:

Не будем создавать функций, сделаем все проще, используем функцию REGEXP_SPLIT_TO_ARRAY вместе с UNNEST. Для этого в SELECT делаем два подзапроса, где формируем массивы, раскрываем, сортируем, собираем в массивы и сравниваем:

select 
 array(select unnest(regexp_split_to_array('привет мир', '')) order by 1) = 
 array(select unnest(regexp_split_to_array('ртпр ивеим', '')) order by 1);
 
?column?|
--------+
true    |

select 
 array(select unnest(regexp_split_to_array('привет мир', '')) order by 1) = 
 array(select unnest(regexp_split_to_array('ртбб ивеим', '')) order by 1);
 
?column?|
--------+
false   |

То есть в данном случае цикл FOR с функциями ARRAY_APPEND и SUBSTRING заменили функцией REGEXP_SPLIT_TO_ARRAY, которая разбивает строку по регулярному выражению и из элементов формирует массив.

​​Модуль TSM_SYSTEM_TIME предоставляет метод извлечения выборки SYSTEM_TIME, который можно использовать в предложении TABLESAMPLE команды SELECT.

Этот метод извлечения выборки принимает в единственном аргументе число с плавающей точкой, задающее максимальное время (в миллисекундах), которое отводится на чтение таблицы. Это даёт возможность непосредственно управлять длительностью выполнения запроса, ценой того, что размер выборки оказывается трудно предсказуемым. Результирующая выборка будет содержать столько строк, сколько удастся прочитать за отведённое время, если только быстрее не будет прочитана вся таблица.

Как и встроенный метод извлечения выборки SYSTEM, SYSTEM_TIME производит выборку на уровне блоков, так что выборка будет не полностью случайной, а может подвергаться эффектам кластеризации, особенно когда выбирается небольшое число строк.

Данный модуль считается «доверенным», то есть его могут устанавливать обычные пользователи, имеющие право CREATE в текущей базе данных.

Для подключения модуля нужно выполнить команду:

create extension tsm_system_time;

Далее для получения какого-то фрагмента данных используем сам модуль к таблице ticket_flights (1 млн записей), запустим его по 3 раза с разным временем:

select count(*)
from ticket_flights tf tablesample system_time(1);

count|
-----+
  600|
      
count|
-----+
  840|
      
count|
-----+
  720|
  
select count(*)
from ticket_flights tf tablesample system_time(100);

count|
-----+
84960|

count |
------+
103560|

count |
------+
113160|

Так как работа идет с блоками, то записи идут подряд, но из разных частей таблицы:

select *
from ticket_flights tf tablesample system_time(1);

ticket_no    |flight_id|fare_conditions|amount  |
-------------+---------+---------------+--------+
0005432044608|     2132|Economy        |11600.00|
0005432044609|     2132|Economy        |11600.00|
0005432108278|     7079|Economy        |16400.00|
0005432108279|     7079|Economy        |16400.00|
0005432108280|     7079|Economy        |16400.00|
0005432154014|     7079|Economy        |16400.00|
0005432154015|     7079|Economy        |16400.00|
0005432154016|     7079|Economy        |16400.00|
0005432154017|     7079|Economy        |16400.00|

​​PL/pgSQL это блочно-структурированный язык. Текст тела функции должен быть блоком. Структура блока:

[ <<метка>> ]
[ DECLARE
    объявления ]
BEGIN
    операторы
END [ метка ];

Каждое объявление и каждый оператор в блоке должны завершаться символом «;» (точка с запятой). Блок, вложенный в другой блок, должен иметь точку с запятой после END, как показано выше. Однако финальный END, завершающий тело функции, не требует точки с запятой.

Метка требуется только тогда, когда нужно идентифицировать блок в операторе EXIT, или дополнить имена переменных, объявленных в этом блоке. Если метка указана после END, то она должна совпадать с меткой в начале блока.

Любой оператор в выполняемой секции блока может быть вложенным блоком. Вложенные блоки используются для логической группировки нескольких операторов или локализации области действия переменных для группы операторов. Во время выполнения вложенного блока переменные, объявленные в нём, скрывают переменные внешних блоков с такими же именами. Чтобы получить доступ к внешним переменным, нужно дополнить их имена меткой блока. Например:

create function foo() returns integer as $$
<<outerblock>>
declare 
 var integer = 100;
begin
 raise notice 'значение var из внешнего блока равно %', var; --100
 -- вложенный блок
 declare
    var integer = 200;
    begin
        raise notice 'сейчас var из внутреннего блока равно %', var; --200
        raise notice 'во внешнем блоке var по прежнему равно %', outerblock.var; --100
 end;
 return var;
end;
$$ language plpgsql;

​​FOUND - это специальная переменная, которая существует в процедурном языке PL/pgSQL. Содержит значение в типе данных boolean.

FOUND является локальной переменной в каждой функции PL/pgSQL и любые её изменения, влияют только на текущую функцию.

При вызове функции на PL/pgSQL, переменная FOUND инициализируется в ложь. Далее, значение переменной изменяется следующими операторами:

▫️ SELECT INTO записывает в FOUND true, если строка присвоена, или false, если строки не были получены.

▫️ PERFORM записывает в FOUND true, если строки выбраны (и отброшены) или false, если строки не выбраны.

▫️ UPDATE, INSERT и DELETE записывают в FOUND true, если при их выполнении была задействована хотя бы одна строка, или false, если ни одна строка не была задействована.

▫️ FETCH записывают в FOUND true, если команда вернула строку, или false, если строка не выбрана.

▫️ MOVE записывают в FOUND true при успешном перемещении курсора, в противном случае — false.

▫️ FOR, как и FOREACH, записывает в FOUND true, если была произведена хотя бы одна итерация цикла, в противном случае — false. При этом значение FOUND будет установлено только после выхода из цикла. Пока цикл выполняется, оператор цикла не изменяет значение переменной. Но другие операторы внутри цикла могут менять значение FOUND.

▫️ RETURN QUERY и RETURN QUERY EXECUTE записывают в FOUND true, если запрос вернул хотя бы одну строку, или false, если строки не выбраны.

Другие операторы PL/pgSQL не меняют значение FOUND.
ВАЖНО! EXECUTE изменяет вывод GET DIAGNOSTICS, но не меняет FOUND.

Создадим тестовую таблицу из одного столбца:

create table test_table (
 var text
);

Напишем функцию, в которой будем получать данные из таблицы test_table, записывать полученный результат в переменную i и в условии проверять, чему будет равна переменная FOUND:

create or replace function foo() returns void as $$
declare 
 i record;
begin
 select * from test_table into i;
 if found then 
  raise exception 'значение found равно %', found;
 else 
  raise exception 'значение found равно %', found;
 end if;
end;
$$ language plpgsql;

Проверим результат:

select foo();

SQL Error [P0001]: ОШИБКА: значение found равно f

Так как в таблице 0 строк, то FOUND равна false.
Добавим несколько записей в таблицу:

insert into test_table
values ('a'), ('b'), ('c');

Проверим теперь результат:

select foo();

SQL Error [P0001]: ОШИБКА: значение found равно t

Так как в таблице есть несколько строк, то FOUND равна true.
При этом нужно помнить, что если при получении результата не используется агрегация, то результат будет пустым и FOUND будет равна false, если же в запросе используется агрегация, то результатом будет одна строка, даже если данных в таблице нет и FOUND будет равна true.
Удалим все данные:

delete from test_table;

Изменим функцию с получением агрегации:

create or replace function foo() returns void as $$
declare 
 i record;
begin
 select count(*) from test_table into i;
 if found then 
  raise exception 'значение found равно %', found;
 else 
  raise exception 'значение found равно %', found;
 end if;
end;
$$ language plpgsql;

Проверим результат:

select foo();

SQL Error [P0001]: ОШИБКА: значение found равно t

Переменная FOUND равна true.

​​PERFORM - используется в PL\pgSQL, данная команда выполняет запрос и отбрасывает получаемый результат.
Синтаксис:

PERFORM запрос;

Запросы пишутся таким же образом, как и в команде SELECT, но ключевое слово SELECT заменяется на PERFORM.

Можно предположить, что такой же результат получается непосредственно командой SELECT, но в настоящее время использование PERFORM является единственным способом. Команда SQL, которая может возвращать строки, например SELECT, будет отклонена с ошибкой, если не имеет предложения INTO.

К примеру, создадим таблицу, в которой будет один столбец, которых хранит результат каких-то вычислений:

create table some_result (
 res bigint
);

Создадим две функции. В первой получаем результат возведения первого аргумента в степень второго аргумента, полученное значение возвращаем в результат и запускаем вторую функцию, где будем записывать полученный аргумент в таблицу some_result:

create function foo1(int, int) returns bigint as $$
declare x bigint;
begin
 x = power($1, $2);
 perform foo2(x);
 return x;
end;
$$ language plpgsql

create function foo2(bigint) returns void as $$
begin
 insert into some_result
 values ($1);
end;
$$ language plpgsql

Так как функция foo2 возвращает VOID, то есть ничего не возвращает в результат, то единственный вариант ее вызывать - это использовать PERFORM.

Запустим функцию foo1 и посмотрим на содержимое таблицы some_result:

select foo1(2, 3);

foo1|
----+
   8|
   
select * from some_result;
   
res|
---+
  8|

А если бы в функции foo1 вместо PERFORM использовали SELECT, то получали бы ошибку:

create or replace function foo1(int, int) returns bigint as $$
declare x bigint;
begin
 x = power($1, $2);
 select foo2(x);
 return x;
end;
$$ language plpgsql

select foo1(2, 3);

SQL Error [42601]: ОШИБКА: в запросе нет назначения для данных результата
  Подсказка: Если вам нужно отбросить результаты SELECT, используйте PERFORM.
  Где: функция PL/pgSQL foo1(integer,integer), строка 5, оператор SQL-оператор

​​GET DIAGNOSTICS - позволяет определить результат команды в PL\pgSQL.
Синтаксис:

GET DIAGNOSTICS переменная = элемент [ , ... ];

Эта команда позволяет получить системные индикаторы состояния. Каждый элемент представляется ключевым словом, указывающим, какое значение состояния нужно присвоить заданной переменной (она должна иметь подходящий тип данных, чтобы принять его). В настоящее время доступны следующие элементы состояния:

Name           | Тип    | Описание                                         |
---------------+--------+--------------------------------------------------+
ROW_COUNT      | bigint | число строк, обработанных последней командой SQL |
PG_CONTEXT     | text   | строки текста, описывающие текущий стек вызовов  |
PG_ROUTINE_OID | oid    | OID текущей функции                              |

Как всегда, сделаем простую тестовую таблицу и внесем в нее 3 строки:

create table a (
 a_id serial,
 a_val text);
 
insert into a (a_val)
values ('a'), ('b'), ('c');

select * from a;

a_id|a_val|
----+-----+
   1|a    |
   2|b    |
   3|c    |

Напишем функцию, где получим данные из тестовой таблицы, через GET DIAGNOSTICS и переменную ROW_COUNT получим количество строк в этой таблице, далее изменим несколько значений и опять же через GET DIAGNOSTICS получим количество измененных значений. Эти значения выведем в результат:

create or replace function foo() returns table (str text) as $$
declare i int;
begin
 perform * from a;
 get diagnostics i = row_count;
 return query select format('в таблице %s строк', i);
 update a 
 set a_val = 'x'
 where a_id in (1, 3, 5);
 get diagnostics i = row_count;
 return query select format('было изменено %s строк', i);
end;
$$ language plpgsql

select * from foo();

str                  |
---------------------+
в таблице 3 строк    |
было изменено 2 строк|

В данном случае мы видим, что планировали изменить 3 записи, но изменение произошло только по 2.

​​Рано или поздно возникает ситуация, когда нужно обработать множество функций за раз. Это может быть разовое удаление множества функций или выдача права EXECUTE на множество функций, но смысл один, мы не хотим обрабатывать каждую функцию по отдельности, нам нужно получить список всех функций и обработать его.
Если пойти в представление information_schema.routines, то там хранятся данные по названиям функций без указания аргументов:

select routine_schema, routine_type, routine_name 
from information_schema.routines
where routine_schema = 'public';

routine_schema|routine_type|routine_name              |
--------------+------------+--------------------------+
public        |FUNCTION    |_group_concat             |
public        |FUNCTION    |ddl_command_audit         |
public        |FUNCTION    |film_in_stock             |
public        |FUNCTION    |film_not_in_stock         |
public        |FUNCTION    |foo                       |

Учитывая, что PostgreSQL поддерживает перегрузку имен функций, то есть может быть несколько функций с одинаковым названием, но разными аргументами, то просто название не позволит обработать функции, нужно получать список аргументов функций и для этого пойдем в pg_catalog и получим нужные данные:

select ns.nspname, proname, proargtypes
from pg_proc 
join pg_namespace ns on pg_proc.pronamespace = ns.oid
where ns.nspname = 'public';

nspname|proname           |proargtypes|
-------+------------------+-----------+
public |_group_concat     |25 25      |
public |ddl_command_audit |           |
public |film_in_stock     |23 23      |
public |film_not_in_stock |23 23      |
public |foo               |           |

proargtypes - атрибут имеет тип данных oidvector и содержит массив типов аргументов функции. В нём учитываются только входные аргументы функции (включая аргументы INOUT и VARIADIC), так что он представляет сигнатуру вызова функции. Для получения фактических названий аргументов нужно использовать функцию OIDVECTORTYPES:

select ns.nspname, proname, proargtypes, oidvectortypes(proargtypes),
 concat(proname, '(', oidvectortypes(proargtypes), ')')
from pg_proc 
join pg_namespace ns on pg_proc.pronamespace = ns.oid
where ns.nspname = 'public';

nspname|proname           |proargtypes|oidvectortypes   |concat                             |
-------+------------------+-----------+-----------------+-----------------------------------+
public |_group_concat     |25 25      |text, text       |_group_concat(text, text)          |
public |ddl_command_audit |           |                 |ddl_command_audit()                |
public |film_in_stock     |23 23      |integer, integer |film_in_stock(integer, integer)    |
public |film_not_in_stock |23 23      |integer, integer |film_not_in_stock(integer, integer)|
public |foo               |           |                 |foo()                              |

Теперь в анонимном блоке DO можем выдать права доступа на все функции для учетной записи. В цикле FOR получаем список всех функций и на каждой итерации через EXECUTE формируем запрос на выдачу прав на каждую функцию в цикле:

do $$
declare 
 funcname text;
begin
 for funcname in 
  select concat(ns.nspname, '.', proname, '(', oidvectortypes(proargtypes), ')')
  from pg_proc 
  join pg_namespace ns on pg_proc.pronamespace = ns.oid
  where ns.nspname = 'public'
 loop
  execute format('grant execute on function %s to some_user', funcname);
 end loop; 
end;
$$ language plpgsql;

Таким образом можно обрабатывать множество функций в разных задачах.

Всем добрый день.
Канал находится в отпуске до 25 числа.
Всем отдохнуть от SQL!

Всем доброго дня.
Есть мысли понемногу возродить канал, но... в свое время Notion ушел из России.
Могу попросить накидать в комментарии варианты автоматизации постинга в ТГ. Вдруг кто с этим сталкивался / сталкивается.
Нужен инструмент, где в редакторе в формате веб интерфейса или MD / HTML можно готовить посты (эти посты уходят на сайт в виде справочника или формирую лонгриды) и бот, который автоматизирует выгрузку в ТГ по расписанию.
Заранее благодарю!