Некоторые полезные функции

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

CREATE OR REPLACE FUNCTION raise_notice(text)
RETURNS void AS
$BODY$
BEGIN
RAISE NOTICE '%', $1;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 1;

Read more »

В ожидании 8.4 - pl/* srf функции в выборках

Перевод Waiting for 8.4 - pl/* srf functions in selects с select * from depesz;

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

Комментарий к патчу:

Расширяет ExecMakeFunctionResult() поддержкой set-returning
функций, возвращающих значения с использованием tuplestore вместо механизма
значение-за-вызов. Проведён рефакторинг некоторых вещей, устраняющий дублирование 
кода с помощью nodeFunctionscan.c. Это не обсуждаемая часть моего патча для перевода
SQL функций на возврат tuplestore. На данный момент SQL функции всё ещё ведут себя 
по старому. Однако, теперь возможно использовать PL SRF функции в целевом списке 
полей.

Что это даёт. Как вы возможно знаете, нельзя сделать join таблицы с функцией. Если функция возвращает >1 строки (или >1 колонки), не возможно её вызвать, передав ей в качестве аргумента поле из таблицы, используемой в запросе.

Я думаю описание немного сложновато, так что сразу перейдём к примеру.

Есть функция, которая, принимая 2 целых значения, возвращает все значения между ними, плюс некоторые текстовые поля:

CREATE OR REPLACE FUNCTION test(
    IN from_i INT4,
    IN to_i INT4,
    OUT numerical INT4,
    OUT textual TEXT
) RETURNS setof record as $$
DECLARE
i INT4;
BEGIN
    for i in from_i .. to_i LOOP
        numerical := i;
        textual := 'i = ' || i;
        RETURN next;
    END loop;
    RETURN;
END;
$$ language plpgsql;

Эта простая функция делает следующее:

# select * from test(2,5);
 numerical | textual
-----------+---------
         2 | i = 2
         3 | i = 3
         4 | i = 4
         5 | i = 5
(4 rows)

Теперь, допустим, мы хотим (по какой-то причине) получить такие строки для каждой пары следующего выражения:

# select 1 as from_i, i as to_i from generate_series(1,3) i;
 from_i | to_i
--------+------
      1 |    1
      1 |    2
      1 |    3
(3 rows)

По большому счёту это затруднительно. Я не могу сделать join generate_series и моей функции. Если бы она была на SQL, то можно было бы сделать так

select i, test(...) from

но она на pl/pgsql. Конечно, я бы мог написать для неё SQL обёртку, но это совсем не здорово. К счастью, с новым патчем, pl/pgsql (и другие pl/*) функции смогут вызываться так же как и просто SQL функции:

# select i, test(1, i) from generate_series(1,3) i;
 i |    test
---+-------------
 1 | (1,"i = 1")
 2 | (1,"i = 1")
 2 | (2,"i = 2")
 3 | (1,"i = 1")
 3 | (2,"i = 2")
 3 | (3,"i = 3")
(6 rows)

Но что, если надо получить колонки отдельно?

Это можно сделать так:

select i, (test(1, i)).numerical, (test(1,i)).textual from generate_series(1,3) i;

Но в этом случае test() будет вызываться дважды для каждой строки, что будет проблематично, если там будет что-нибудь посложнее, чем просто цикл.

К счастью это можно также сделать простым подзапросом:

# select i, (test).numerical, (test).textual
    from (select i, test(1, i) from generate_series(1,3) i) x;
 i | numerical | textual
---+-----------+---------
 1 |         1 | i = 1
 2 |         1 | i = 1
 2 |         2 | i = 2
 3 |         1 | i = 1
 3 |         2 | i = 2
 3 |         3 | i = 3
(6 rows)

Превосходно. Ещё одна проблема позади.

*ДОПОЛНЕНИЕ*

(после напоминания от David Fetter)

CTE (основные табличные выражения) настолько новы, что я просто о них забыл, вот они:

with source as (
   select i, test(1, i) from generate_series(1,3) i
)
select i, (test).numerical, (test).textual from source;

explain analyze

для CTE:

                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 CTE Scan on source  (cost=262.50..282.50 rows=1000 width=36) (actual time=2.331..2.534 rows=6 loops=1)
   InitPlan
     ->  Function Scan on generate_series i  (cost=0.00..262.50 rows=1000 width=4) (actual time=2.315..2.488 rows=6 loops=1)
 Total runtime: 2.646 ms
(4 rows)

и для подзапроса:

                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Subquery Scan x  (cost=0.00..272.50 rows=1000 width=36) (actual time=0.236..0.423 rows=6 loops=1)
   ->  Function Scan on generate_series i  (cost=0.00..262.50 rows=1000 width=4) (actual time=0.229..0.395 rows=6 loops=1)
 Total runtime: 0.477 ms
(3 rows)

От автора перевода:

Немного не понятно, почему в первом случае Function Scan занял больше времени. Возможно это было связано с разной нагрузкой на сервер во время обоих тестов. И я думаю, в данном случае смотреть надо на estimation, а не на actual time. Буду рад почитать ваши мысли по этому поводу в комментариях.

Кстати, я это не тестировал, но мне кажется, что достичь желаемого результата и без двойного вызова можно так

select i, (test(1, i)).* from generate_series(1,3) i;

Или я ошибаюсь?

*ДОПОЛНЕНИЕ*

Да, я оказался прав

# select i, (test(1, i)).* from generate_series(1,3) i;
i | numerical | textual
—+———–+———
1 | 1 | i = 1
2 | 1 | i = 1
2 | 2 | i = 2
3 | 1 | i = 1
3 | 2 | i = 2
3 | 3 | i = 3
(6 rows)

В ожидании 8.4 - RETURNING в подзапросах

Перевод Waiting for 8.4 - sql-wrappable RETURNING с select * from depesz;

В PostgreSQL 8.2 было добавлено выражение RETURNING для INSERT/UPDATE/DELETE запросов. К сожалению, оно не могло быть использовано как источник строк для всего в SQL.

insert into table_backup delete from table where ... returning *;

В прочем, это и сейчас не возможно, но был сделан один шаг в правильном направлении, благодаря патчу от Tom Lane 31го Октября:

Позволяет SQL-функциям возвращать вывод INSERT/UPDATE/DELETE RETURNING выражений, а не только SELECT как прежде.

Дополнительный эффект этого патча таков, что когда возвращающая множество SQL функция используется в FROM, производительность увеличивается за счёт того, что вывод накапливается в tuplestore внутри функции, в отличие от менее эффективного значение-за-вызов механизма.

Как это работает? Всё просто. Начнём с тестовой таблицы:

# create table test (i int4);
CREATE TABLE

С тестовым контентом:

# insert into test select generate_series(1, 10);
INSERT 0 10

Теперь создадим свою SQL функцию удаляющую строки:

CREATE function delete_from_test_returning(INT4) RETURNS setof test as $$
    DELETE FROM test WHERE i <= $1 returning *
$$ language sql;

Как видно, функция очень проста.

Теперь используем её для бэкапа удаленных строк:

# create table delete_backup as select * from delete_from_test_returning(3);
SELECT

И проверим содержание обеих таблиц:

# select * from test;
 i
----
  4
  5
  6
  7
  8
  9
 10

(7 rows)

# select * from delete_backup;
 i
---
 1
 2
 3

(3 rows)

Конечно, я мог бы сделать это раньше в pl/pgsql функции, которая бы пробегалась по всем возвращаемым строкам, но в данном случае это определённо будет быстрее.

В ожидании 8.4 - \ef в psql

Перевод Waiting for 8.4 - \ef in psql с select * from depesz;

Сегодня Tom Lane применил патч, написанный Abhijit Menon-Sen, который добавляет интересную возможность в psql. А именно - упрощает изменение определения функций.

Сообщение патча наиболее полно всё объясняет:

Реализация psql комманды "\ef" для редактирования определения функции.
В поддержку этого, создание backend-функции pg_get_functiondef().
Комманда функциональна, но, возможно, потребует небольших улучшений...

Abhijit Menon-Sen

Более подробно.

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

Другим способом было хранение creation.sql файла, и его изменение когда необходимо изменить функцию.

Но сейчас всё стало проще. Достаточно вызвать \ef function_name и предпочитаемый вами редактор ($EDITOR) откроется с полным "CREATE OR REPLACE FUNCTION..." на редактирование.

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

# \ef texticlike
ERROR:  more than one function named "texticlike"
LINE 1: SELECT 'texticlike'::pg_catalog.regproc::pg_catalog.oid
               ^

И тогда вы сможете вызвать \ef с параметрами функции:

# \ef texticlike(citext, text)

Великолепное дополнение. psql становится всё лучше и лучше.

Параметры по умолчанию для PL функций

Перевод default parameters for PL functions с Pavel Stehule's blog

Привет

Я закончил работу над одной из задач - значения по умолчанию для PL функций. Применение простое - такое же как значений по умолчанию в Firebird 2.x.

postgres=# create or replace function x1(int = 1,int = 2,int= 3)
       returns int as $$
         select $1+$2+$3;
       $$ language sql;
CREATE FUNCTION
postgres=# select x1();
x1
----
6
(1 row)

postgres=# select x1(10);;
x1
----
15
(1 row)

postgres=# select x1(10,20);
x1
----
33
(1 row)

postgres=# select x1(10,20,30);
x1
----
60
(1 row)

Это первый шаг - и менее обсуждаемый. Второй шаг будет сложным - существует два мнения по поводу синтаксиса именованных параметров: вариант a) подобный Oracle синтаксис name => expression и вариант b) собственный синтаксис на основе "AS" expression AS name. Я предпочитаю вариант (a) - думаю это более читабельно (AS в SQL используется для меток). Вариант (b) надёжен с точки зрения совместимости. На pg_hackers было обсуждение без какого-либо заключения. Так что, я надеюсь, по крайней мере значения по умолчанию будут включены в проект.

пока
Pavel