Рефакторинг функции PL/pgSQL для возврата результатов различных запросов SELECT
Я написал функцию, которая выводит PostgreSQL SELECT
запрос хорошо сформирован в текстовом виде. Теперь я больше не хочу выводить текст, но на самом деле запускаю сгенерированный SELECT
утверждение против базы данных и вернуть результат - так же, как и сам запрос.
Что у меня так далеко:
CREATE OR REPLACE FUNCTION data_of(integer)
RETURNS text AS
$BODY$
DECLARE
sensors varchar(100); -- holds list of column names
type varchar(100); -- holds name of table
result text; -- holds SQL query
-- declare more variables
BEGIN
-- do some crazy stuff
result := 'SELECT\r\nDatahora,' || sensors ||
'\r\n\r\nFROM\r\n' || type ||
'\r\n\r\nWHERE\r\id=' || $1 ||'\r\n\r\nORDER BY Datahora;';
RETURN result;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION data_of(integer) OWNER TO postgres;
sensors
содержит список имен столбцов для таблицы type
, Они объявлены и заполнены в ходе функции. В конце концов, они содержат такие значения, как:
sensors
:'column1, column2, column3'
За исключениемDatahora
(timestamp
) все столбцы имеют типdouble precision
,type
:'myTable'
Может быть именем одной из четырех таблиц. У каждого есть разные столбцы, кроме общего столбцаDatahora
,
Переменная sensors
будет содержать все столбцы, отображаемые здесь для соответствующей таблицы в type
, Например: если type
является pcdmet
затем sensors
будет 'datahora,dirvento,precipitacao,pressaoatm,radsolacum,tempar,umidrel,velvento'
Переменные используются для построения SELECT
заявление, которое хранится в result
, Подобно:
SELECT Datahora, column1, column2, column3
FROM myTable
WHERE id=20
ORDER BY Datahora;
Прямо сейчас моя функция возвращает это утверждение как text
, Я копирую-вставляю и выполняю его в pgAdmin или через psql. Я хочу автоматизировать это, выполнить запрос автоматически и вернуть результат. Как я могу это сделать?
3 ответа
Динамический SQL и RETURN
тип
(Я сохранил лучшее для последнего, продолжайте читать!)
Вы хотите выполнить динамический SQL. В принципе это просто в plpgsql с помощью EXECUTE
, Вам не нужен курсор - на самом деле, в большинстве случаев вам лучше без явных курсоров.
Найдите примеры на SO с поиском.
Проблема, с которой вы столкнулись: вы хотите вернуть записи еще неопределенного типа. Функция должна объявить тип возвращаемого значения с RETURNS
пункт (или с OUT
или же INOUT
параметры). В вашем случае вам придется прибегнуть к анонимным записям, так как количество, имена и типы возвращаемых столбцов различаются. Подобно:
CREATE FUNCTION data_of(integer)
RETURNS SETOF record AS ...
Однако это не особенно полезно. Таким образом, вы должны будете предоставить список определений столбцов при каждом вызове функции. Подобно:
SELECT * FROM data_of(17)
AS foo (colum_name1 integer
, colum_name2 text
, colum_name3 real);
Но как бы вы это сделали, если не знаете заранее столбцы?
Вы можете прибегнуть к менее структурированным типам данных документа, таким как json
, jsonb
, hstore
или же xml
:
Но для целей этого вопроса давайте предположим, что вы хотите вернуть как можно больше отдельных, правильно набранных и именованных столбцов.
Простое решение с фиксированным типом возврата
Колонка datahora
кажется заданным, я буду считать тип данных timestamp
и что всегда есть еще два столбца с различным именем и типом данных.
Имена, которые мы оставим в пользу общих имен в возвращаемом типе.
Типы мы тоже откажемся и бросим все на text
поскольку каждый тип данных может быть приведен к text
,
CREATE OR REPLACE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, col2 text, col3 text) AS
$func$
DECLARE
_sensors text := 'col1::text, col2::text'; -- cast each col to text
_type text := 'foo';
BEGIN
RETURN QUERY EXECUTE '
SELECT datahora, ' || _sensors || '
FROM ' || quote_ident(_type) || '
WHERE id = $1
ORDER BY datahora'
USING _id;
END
$func$ LANGUAGE plpgsql;
Как это работает?
Переменные
_sensors
а также_type
может быть входными параметрами вместо.Обратите внимание
RETURNS TABLE
пункт.Обратите внимание на использование
RETURN QUERY EXECUTE
, Это один из самых элегантных способов вернуть строки из динамического запроса.Я использую имя для параметра функции, просто чтобы сделать
USING
пункт оRETURN QUERY EXECUTE
менее запутанным.$1
в SQL-строке относится не к параметру функции, а к значению, переданному сUSING
пункт. (Оба случайно$1
в соответствующем объеме в этом простом примере.)Обратите внимание на пример значения для
_sensors
: каждый столбец приведен к типуtext
,Этот вид кода очень уязвим для внедрения SQL. я использую
quote_ident()
защитить от этого. Объединение нескольких имен столбцов в переменной_sensors
предотвращает использованиеquote_ident()
(и, как правило, это плохая идея!). Убедитесь, что в этом нет ничего плохого, например, путем индивидуального запуска имен столбцов черезquote_ident()
вместо.VARIADIC
параметр приходит на ум...
Проще с PostgreSQL 9.1+
С версией 9.1 или выше вы можете использовать format()
для дальнейшего упрощения:
RETURN QUERY EXECUTE format('
SELECT datahora, %s -- identifier passed as unescaped string
FROM %I -- assuming the name is provided by user
WHERE id = $1
ORDER BY datahora'
,_sensors, _type)
USING _id;
Опять же, имена отдельных столбцов могут быть экранированы правильно и будут чистым способом.
Переменное количество столбцов с одинаковым типом
После обновления вашего вопроса похоже, что ваш тип возврата имеет
- переменное количество столбцов
- но все столбцы одного типа
double precision
(псевдонимfloat8
)
Как мы должны определить RETURN
тип функции, к которой я прибегаю ARRAY
введите в этом случае, который может содержать переменное количество значений. Кроме того, я возвращаю массив с именами столбцов, чтобы вы могли также проанализировать имена из результата:
CREATE OR REPLACE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, names text[], values float8[] ) AS
$func$
DECLARE
_sensors text := 'col1, col2, col3'; -- plain list of column names
_type text := 'foo';
BEGIN
RETURN QUERY EXECUTE format('
SELECT datahora
, string_to_array($1) -- AS names
, ARRAY[%s] -- AS values
FROM %s
WHERE id = $2
ORDER BY datahora'
, _sensors, _type)
USING _sensors, _id;
END
$func$ LANGUAGE plpgsql;
Различные полные типы таблиц
Если вы на самом деле пытаетесь вернуть все столбцы таблицы (например, одну из таблиц на связанной странице, то используйте это простое, очень мощное решение с полиморфным типом:
CREATE OR REPLACE FUNCTION data_of(_tbl_type anyelement, _id int)
RETURNS SETOF anyelement AS
$func$
BEGIN
RETURN QUERY EXECUTE format('
SELECT *
FROM %s -- pg_typeof returns regtype, quoted automatically
WHERE id = $1
ORDER BY datahora'
, pg_typeof(_tbl_type))
USING _id;
END
$func$ LANGUAGE plpgsql;
Вызов:
SELECT * FROM data_of(NULL::pcdmet, 17);
замещать pcdmet
в вызове с любым другим именем таблицы.
Как это работает?
anyelement
является псевдо-типом данных, полиморфным типом, заполнителем для любого типа данных, не являющегося массивом. Все случаиanyelement
в функции оценивать тот же тип, который предоставляется во время выполнения. Предоставляя значение определенного типа в качестве аргумента функции, мы неявно определяем тип возвращаемого значения.PostgreSQL автоматически определяет тип строки (составной тип данных) для каждой созданной таблицы, поэтому для каждой таблицы существует четко определенный тип. Это включает временные таблицы, что удобно для специального использования.
Любой тип может быть
NULL
, Итак, мы сдаемNULL
значение, приведенное к типу таблицы.Теперь функция возвращает четко определенный тип строки, и мы можем использовать
SELECT * FROM data_of(...)
разложить строку и получить отдельные столбцы.pg_typeof(_tbl_type)
возвращает имя таблицы как тип идентификатора объектаregtype
, Когда автоматически конвертируется вtext
, идентификаторы автоматически заключаются в двойные кавычки и уточняются при необходимости. Следовательно, SQL-инъекция невозможна. Это может даже иметь дело с полными именами таблиц, гдеquote_ident()
потерпит неудачу
Возможно, вы захотите вернуть курсор. Попробуйте что-то вроде этого (я не пробовал):
CREATE OR REPLACE FUNCTION data_of(integer)
RETURNS refcursor AS
$BODY$
DECLARE
--Declaring variables
ref refcursor;
BEGIN
-- make sure `sensors`, `type`, $1 variable has valid value
OPEN ref FOR 'SELECT Datahora,' || sensors ||
' FROM ' || type ||
' WHERE nomepcd=' || $1 ||' ORDER BY Datahora;';
RETURN ref;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION data_of(integer) OWNER TO postgres;
Извините, но ваш вопрос очень неясен. Однако ниже вы найдете автономный пример того, как создать и использовать функцию, которая возвращает переменную курсора. Надеюсь, поможет!
begin;
create table test (id serial, data1 text, data2 text);
insert into test(data1, data2) values('one', 'un');
insert into test(data1, data2) values('two', 'deux');
insert into test(data1, data2) values('three', 'trois');
create function generate_query(query_name refcursor, columns text[])
returns refcursor
as $$
begin
open query_name for execute
'select id, ' || array_to_string(columns, ',') || ' from test order by id';
return query_name;
end;
$$ language plpgsql;
select generate_query('english', array['data1']);
fetch all in english;
select generate_query('french', array['data2']);
fetch all in french;
move absolute 0 from french; -- do it again !
fetch all in french;
select generate_query('all_langs', array['data1','data2']);
fetch all in all_langs;
-- this will raise in runtime as there is no data3 column in the test table
select generate_query('broken', array['data3']);
rollback;
# copy paste me into bash shell directly
clear; IFS='' read -r -d '' sql_code << 'EOF_SQL_CODE'
CREATE OR REPLACE FUNCTION func_get_all_users_roles()
-- define the return type of the result set as table
-- those datatypes must match the ones in the src
RETURNS TABLE (
id bigint
, email varchar(200)
, password varchar(200)
, roles varchar(100)) AS
$func$
BEGIN
RETURN QUERY
-- start the select clause
SELECT users.id, users.email, users.password, roles.name as roles
FROM user_roles
LEFT JOIN roles ON (roles.guid = user_roles.roles_guid)
LEFT JOIN users ON (users.guid = user_roles.users_guid)
-- stop the select clause
;
END
$func$ LANGUAGE plpgsql;
EOF_SQL_CODE
# create the function
psql -d db_name -c "$sql_code";
# call the function
psql -d db_name -c "select * from func_get_all_users_roles() "