=> DO $$ DECLARE cmd text := 'CREATE TABLE city_msk (id SERIAL, qty INT, d DATE)'; BEGIN EXECUTE cmd; -- таблица для Москвы cmd := replace(cmd, 'city_msk', 'city_brn'); EXECUTE cmd; -- таблица для Барнаула END; $$;
DO
Создаем две таблицы с одинаковой структурой. Для этого текст команд CREATE TABLE передаем в качестве строки в EXECUTE.
Убедимся, что таблицы созданы:
=> \dt
List of relations
Schema | Name | Type | Owner
--------+----------+-------+---------
public | city_brn | table | student
public | city_msk | table | student
(2 rows)
=> DO $$ DECLARE cmd text := 'INSERT INTO city_msk (qty,d) VALUES (round(random()*100), current_date) RETURNING id, qty, d'; rec record; BEGIN EXECUTE cmd INTO rec; RAISE NOTICE 'id : %, qty: %, d: %', rec.id, rec.qty, rec.d; END; $$;
NOTICE: id : 1, qty: 48, d: 2017-09-01 DO
Фраза INTO позволяет вернуть ровно одну строку (первую если несколько).
Для проверки результата выполнения динамической команды можно использовать FOUND и GET DIAGNOSTICS, как и в случае статических команд.
=> DO $$ DECLARE -- Добавим в таблицу Барнаула по строке за каждый день года s text := to_char(current_date, 'YYYY') || '-01-01'; e text := to_char(current_date, 'YYYY') || '-12-31'; cmd text := format( 'INSERT INTO city_brn (qty,d) SELECT round(random()*100), y.d FROM generate_series( %L::timestamp,%L::timestamp,%L::interval ) as y(d)', s, e, '1 day'); cmd_count bigint; BEGIN EXECUTE cmd; GET DIAGNOSTICS cmd_count = ROW_COUNT; RAISE NOTICE 'Добавлено строк: %', cmd_count; END; $$;
NOTICE: Добавлено строк: 365 DO
Для вставки литералов используются спецификаторы %L функции format.
=> DO $$ DECLARE cmd text := 'SELECT id, qty FROM city_brn WHERE d = $1'; query_date1 date := current_date; query_date2 date := current_date - '1 day'::interval; rec record; BEGIN EXECUTE cmd INTO rec USING query_date1; RAISE NOTICE '%: id : %, qty: %', query_date1, rec.id, rec.qty; EXECUTE cmd INTO rec USING query_date2; RAISE NOTICE '%: id : %, qty: %', query_date2, rec.id, rec.qty; END; $$;
NOTICE: 2017-09-01: id : 244, qty: 87 NOTICE: 2017-08-31: id : 243, qty: 18 DO
Если динамический запрос использует параметры, то нужно использовать фразу USING.
Этот же запрос можно выполнить с функцией format вместо параметра (USING).
=> DO $$ DECLARE query_date date := current_date; cmd text := format( 'SELECT id, qty FROM city_brn WHERE d = %L::date', query_date::text ); rec record; BEGIN EXECUTE cmd INTO rec; RAISE NOTICE '%: id : %, qty: %', query_date, rec.id, rec.qty; END; $$;
NOTICE: 2017-09-01: id : 244, qty: 87 DO
Но отказ от использования параметров вынуждает выполнить два лишних приведения типов. Сначала переменная типа DATE приводится к строке, а затем, на этапе выполнения, строка обратно приводится к DATE.
Фразу USING нельзя использовать для имен объектов (названия таблиц, столбцов, пр.) в динамической команде. Поэтому используем спецификатор %I функции format для вставки имени таблицы.
=> CREATE FUNCTION get_qty (tab text, d date) RETURNS int AS $$ DECLARE cmd text := format('SELECT qty FROM %I WHERE d = $1', get_qty.tab); retval int; BEGIN EXECUTE cmd INTO retval USING get_qty.d; RETURN retval; END; $$ LANGUAGE plpgsql;
CREATE FUNCTION
=> select get_qty('city_msk', current_date) as qty_msk, get_qty('city_brn', current_date) as qty_brn;
qty_msk | qty_brn
---------+---------
48 | 87
(1 row)
Теперь имя таблицы можно указывать при вызове функции.
=> DO $do$ DECLARE -- Формируем строку, используя экранирование знаками $$ cmd text := $cmd$SELECT date_trunc('quarter', d)::date AS quarter, sum(qty) AS sum_qty FROM city_brn GROUP BY date_trunc('quarter', d) ORDER BY 1$cmd$; rec record; BEGIN FOR rec IN EXECUTE cmd LOOP RAISE NOTICE 'quarter %: sum_qty: %', rec.quarter, rec.sum_qty; END LOOP; END; $do$;
NOTICE: quarter 2017-01-01: sum_qty: 4475 NOTICE: quarter 2017-04-01: sum_qty: 4662 NOTICE: quarter 2017-07-01: sum_qty: 4683 NOTICE: quarter 2017-10-01: sum_qty: 4356 DO
Результат динамического запроса можно обработать в цикле FOR.
Этот же пример с использованием курсора.
=> DO $do$ DECLARE cmd text := $cmd$SELECT date_trunc('quarter', d)::date AS quarter, sum(qty) AS sum_qty FROM city_brn GROUP BY date_trunc('quarter', d) ORDER BY 1$cmd$; cur REFCURSOR; rec record; BEGIN OPEN cur FOR EXECUTE cmd; FETCH cur INTO rec; RAISE NOTICE 'quarter %: sum_qty: %', rec.quarter, rec.sum_qty; FETCH cur INTO rec; RAISE NOTICE 'quarter %: sum_qty: %', rec.quarter, rec.sum_qty; -- и т.д. CLOSE cur; END; $do$;
NOTICE: quarter 2017-01-01: sum_qty: 4475 NOTICE: quarter 2017-04-01: sum_qty: 4662 DO
Оператор RETURN QUERY для возврата строк из функции также может использовать динамические запросы.
=> CREATE FUNCTION sel_city (tab text, lmt bigint = 0, off bigint = 0) RETURNS SETOF record AS $$ DECLARE cmd text := format('SELECT * FROM %I ORDER BY 1 LIMIT $1 OFFSET $2', tab); BEGIN RETURN QUERY EXECUTE cmd USING lmt, off; END; $$ LANGUAGE plpgsql;
CREATE FUNCTION
Функция возвращает набор записей неопределенной структуры, поэтому такой вызов вернет ошибку:
=> SELECT * FROM sel_city ('city_brn', 5);
ERROR: a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM sel_city ('city_brn', 5);
^
При вызове мы должны явно указать столбцы и типы данных, возвращаемые функцией:
=> SELECT * FROM sel_city ('city_brn', 5) as brn(id int, qty int, d date);
id | qty | d ----+-----+------------ 1 | 6 | 2017-01-01 2 | 21 | 2017-01-02 3 | 27 | 2017-01-03 4 | 12 | 2017-01-04 5 | 2 | 2017-01-05 (5 rows)
=> SELECT * FROM sel_city ('city_brn', 5, 5) as brn(id int, qty int, d date);
id | qty | d ----+-----+------------ 6 | 71 | 2017-01-06 7 | 22 | 2017-01-07 8 | 10 | 2017-01-08 9 | 65 | 2017-01-09 10 | 33 | 2017-01-10 (5 rows)
Конец демонстрации.