Рефакторинг функции 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() "
Другие вопросы по тегам