В код PL/pgSQL можно встраивать команды SQL. Наверное, наиболее часто используемый вариант - команда SELECT, возвращающая одну строку. Пример, который не получилось бы выполнить с помощью выражения с подзапросом (потому что возвращаются сразу два значения):
=> CREATE TABLE t(id integer, code text);
CREATE TABLE
=> INSERT INTO t VALUES (1, 'Раз'), (2, 'Два');
INSERT 0 2
=> DO $$ DECLARE r record; BEGIN SELECT id, code INTO r FROM t WHERE id = 1; RAISE NOTICE '%', r; END; $$;
NOTICE: (1,Раз) DO
Другой вариант - использовать не одну переменную составного типа, а несколько скалярных переменных для каждого поля.
Получится ли вот так?
=> DO $$ DECLARE id integer := 1; code text; BEGIN SELECT id, code INTO id, code FROM t WHERE id = id; RAISE NOTICE '%, %', id, code; END; $$;
Не получится из-за неоднозначности в SELECT: id может означать и имя столбца, и имя переменной:
ERROR: column reference "id" is ambiguous
LINE 1: SELECT id, code FROM t WHERE id = id
^
DETAIL: It could refer to either a PL/pgSQL variable or a table column.
QUERY: SELECT id, code FROM t WHERE id = id
CONTEXT: PL/pgSQL function inline_code_block line 6 at SQL statement
Причем во фразе INTO неоднозначности нет - она относится только к PL/pgSQL. В сообщении, кстати, видно, как PL/pgSQL вырезает фразу INTO, прежде чем передать запрос в SQL.
Есть несколько подходов к устранению неоднозначностей.
Первый состоит в том, чтобы неоднозначностей не допускать. Для этого к переменным добавляют префикс, который обычно выбирается в зависимости от "класса" переменной, например:
* Для параметров p_ (parameter);
* Для обычных переменных l_ (local) или v_ (variable);
* Для констант c_ (constant);
Это простой и действенный способ, если использовать его систематически и никогда не использовать префиксы в именах столбцов. К минусам можно отнести некоторую неряшливость и пестроту кода из-за лишних подчеркиваний.
Вот как это может выглядеть в нашем случае:
=> DO $$ DECLARE l_id integer := 1; l_code text; BEGIN SELECT id, code INTO l_id, l_code FROM t WHERE id = l_id; RAISE NOTICE '%, %', l_id, l_code; END; $$;
NOTICE: 1, Раз DO
Второй способ состоит в использовании квалифицированных имен - к имени объекта через точку дописывается уточняющий квалификатор:
* Для столбца - имя или псевдоним таблицы;
* Для переменной - имя блока;
* Для параметра - имя функции.
Такой способ более "честный", чем добавление префиксов, поскольку работает для любых названий столбцов.
Вот как будет выглядеть наш пример с использованием квалификаторов:
=> DO $$ <<local>> DECLARE id integer := 1; code text; BEGIN SELECT t.id, t.code INTO local.id, local.code FROM t WHERE t.id = local.id; RAISE NOTICE '%, %', id, code; END; $$;
NOTICE: 1, Раз DO
Третий вариант - установить приоритет переменных над столбцами или наоборот, столбцов над переменными. За это отвечает конфигурационный параметр plpgsql.variable_conflict.
В ряде случаев это упрощает разрешение конфликтов, но не устраняет их полностью. Кроме того, неявное правило (которое, к тому же, может внезапно поменяться) непременно приведет к тому, что какой-то код будет выполняться не так, как предполагал разработчик.
Тем не менее приведем пример. Здесь устанавливается приоритет переменных, поэтому достаточно квалифицировать только столбцы таблицы:
=> SET plpgsql.variable_conflict = use_variable;
SET
=> DO $$ DECLARE id integer := 1; code text; BEGIN SELECT t.id, t.code INTO id, code FROM t WHERE t.id = id; RAISE NOTICE '%, %', id, code; END; $$;
NOTICE: 1, Раз DO
=> RESET plpgsql.variable_conflict;
RESET
Какой способ выбрать - дело опыта и вкуса. Мы рекомендуем остановиться либо на первом (префиксы), либо на втором (квалификаторы), и не смешивать их в одном проекте, поскольку систематичность крайне важна для облегчения понимания кода.
В курсе мы будем использовать второй способ, но только в тех случаях, когда это действительно необходимо - чтобы не загромождать примеры.
Однако в коде, предназначенном для промышленной эксплуатации, думать о неоднозначностях надо всегда: нет никакой гарантии, что завтра в таблице не появится новый столбец с именем, совпадающим с вашей переменной!
Что произойдет, если запрос вернет несколько строк?
=> DO $$ DECLARE r record; BEGIN SELECT id, code INTO r FROM t; RAISE NOTICE '%', r; END; $$;
В переменную будет записана только первая строка. Поскольку мы не указали ORDER BY, то результат в общем случае будет непредсказуемым.
NOTICE: (1,Раз) DO
А если запрос не вернет ни одной строки?
=> DO $$ DECLARE r record; BEGIN r := (-1,'!!!'); SELECT id, code INTO r FROM t WHERE false; RAISE NOTICE '%', r; END; $$;
Переменные будут содержать неопределенные значения.
NOTICE: (,) DO
Иногда хочется быть уверенным, что в результате выборки получилась ровно одна строка: ни больше, ни меньше. В этом случае удобно воспользоваться фразой INTO STRICT:
=> DO $$ DECLARE r record; BEGIN SELECT id, code INTO STRICT r FROM t; RAISE NOTICE '%', r; END; $$;
ERROR: query returned more than one row CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement
=> DO $$ DECLARE r record; BEGIN SELECT id, code INTO STRICT r FROM t WHERE false; RAISE NOTICE '%', r; END; $$;
ERROR: query returned no rows CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement
Другая возможность - проверять состояние последней выполненной SQL-команды.
=> DO $$ DECLARE r record; rowcount integer; BEGIN SELECT id, code INTO r FROM t WHERE false; GET DIAGNOSTICS rowcount = ROW_COUNT; RAISE NOTICE 'rowcount = %', rowcount; RAISE NOTICE 'found = %', FOUND; END; $$;
NOTICE: rowcount = 0 NOTICE: found = f DO
=> DO $$ DECLARE r record; rowcount integer; BEGIN SELECT id, code INTO r FROM t; GET DIAGNOSTICS rowcount = ROW_COUNT; RAISE NOTICE 'rowcount = %', rowcount; RAISE NOTICE 'found = %', FOUND; END; $$;
NOTICE: rowcount = 1 NOTICE: found = t DO
Но факт, что запросу соответствует нескольких строк, таким способом обнаружить невозможно.
Если результат запроса не нужен, можно не использовать фиктивные переменные, а заменить SELECT на PERFORM.
=> CREATE FUNCTION do_something() RETURNS void AS $$ BEGIN RAISE NOTICE 'Что-то сделалось.'; END; $$ LANGUAGE plpgsql;
CREATE FUNCTION
=> DO $$ BEGIN PERFORM do_something(); END; $$;
NOTICE: Что-то сделалось. DO
Внутри PL/pgSQL можно использовать без изменений практически любые команды SQL, не возвращающие результат:
=> DO $$ DECLARE rowcount integer; BEGIN CREATE TABLE test(n integer); INSERT INTO test VALUES (1),(2),(3); GET DIAGNOSTICS rowcount = ROW_COUNT; RAISE NOTICE '%', rowcount; UPDATE test SET n = n + 1 WHERE n > 1; GET DIAGNOSTICS rowcount = ROW_COUNT; RAISE NOTICE '%', rowcount; DELETE FROM test WHERE n = 1; GET DIAGNOSTICS rowcount = ROW_COUNT; RAISE NOTICE '%', rowcount; DROP TABLE test; END; $$;
NOTICE: 3 NOTICE: 2 NOTICE: 1 DO
Команды INSERT, UPDATE, DELETE могут возвращать результат с помощью фразы RETURNING. Их можно использовать в PL/pgSQL точно так же, как SELECT, добавив фразу INTO:
=> DO $$ DECLARE r record; BEGIN UPDATE t SET code = code || '!' WHERE id = 1 RETURNING * INTO r; RAISE NOTICE 'Изменили: %', r; RAISE NOTICE 'found = %', FOUND; END; $$;
NOTICE: Изменили: (1,Раз!) NOTICE: found = t DO
=> SELECT * FROM t;
id | code ----+------ 2 | Два 1 | Раз! (2 rows)
Если команда не затронет ни одной строки, получим неопределенные значения:
=> DO $$ DECLARE r record; BEGIN UPDATE t SET code = code || '!' WHERE id = -1 RETURNING * INTO r; RAISE NOTICE 'Изменили: %', r; RAISE NOTICE 'found = %', FOUND; END; $$;
NOTICE: Изменили: (,) NOTICE: found = f DO
Поскольку в INSERT, UPDATE, DELETE нет возможности указать порядок строк (ORDER BY), команда, затрагивающая несколько строк, приведет к ошибке даже без указания STRICT:
=> DO $$ DECLARE r record; BEGIN UPDATE t SET code = code || '!' RETURNING * INTO r; RAISE NOTICE 'Изменили: %', r; END; $$;
ERROR: query returned more than one row CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement
Но фраза STRICT позволяет гарантировать, что строка будет ровно одна (а не ноль):
=> DO $$ DECLARE r record; BEGIN UPDATE t SET code = code || '!' WHERE id = -1 RETURNING * INTO STRICT r; RAISE NOTICE 'Изменили: %', r; END; $$;
ERROR: query returned no rows CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement
Пример табличной функции на PL/pgSQL:
=> CREATE FUNCTION t() RETURNS TABLE(LIKE t) AS $$ BEGIN RETURN QUERY SELECT id, code FROM t ORDER BY id; END; $$ STABLE LANGUAGE plpgsql;
CREATE FUNCTION
=> SELECT * FROM t();
id | code ----+------ 1 | Раз! 2 | Два (2 rows)
Другой вариант - возвращать значения построчно.
=> CREATE FUNCTION days_of_week() RETURNS SETOF text AS $$ BEGIN FOR i IN 7 .. 13 LOOP RETURN NEXT to_char(to_date(i::text,'J'),'TMDy'); END LOOP; END; $$ STABLE LANGUAGE plpgsql;
CREATE FUNCTION
=> SELECT * FROM days_of_week() WITH ORDINALITY;
days_of_week | ordinality --------------+------------ Пн | 1 Вт | 2 Ср | 3 Чт | 4 Пт | 5 Сб | 6 Вс | 7 (7 rows)
Почему функция объявлена как STABLE?
Потому что, хотя значение функции не зависит ни от параметров, ни от данных, оно тем не менее неявно зависит от текущей локали:
=> SET lc_time = 'en_US.UTF8';
SET
=> SELECT * FROM days_of_week() WITH ORDINALITY;
days_of_week | ordinality --------------+------------ Mon | 1 Tue | 2 Wed | 3 Thu | 4 Fri | 5 Sat | 6 Sun | 7 (7 rows)
Конец демонстрации.