Использовать вывод текста из функции в качестве нового запроса

Продолжая предыдущий случай, которому помогали @Erwin Brandstetter и @Craig Ringer, я исправил свой код следующим образом. Обратите внимание, что моя функция myresult() выходы сейчас textи не таблица (как, как было указано в предыдущем случае, нет смысла выводить табличный объект, поскольку нам нужно было бы определить все его столбцы впереди, что в принципе не поддается цели):

CREATE OR REPLACE FUNCTION myresult(mytable text, myprefix text)
RETURNS text AS 
$func$
DECLARE
   myoneliner text;
BEGIN
   SELECT INTO myoneliner  
          'SELECT '
        || string_agg(quote_ident(column_name::text), ',' ORDER BY column_name)
        || ' FROM ' || quote_ident(mytable)
   FROM   information_schema.columns
   WHERE  table_name = mytable
   AND    column_name LIKE myprefix||'%'
   AND    table_schema = 'public';  -- schema name; might be another param

   RAISE NOTICE 'My additional text: %', myoneliner;
   RETURN myoneliner;
END
$func$ LANGUAGE plpgsql;

Вызов:

select myresult('dkj_p_k27ac','enri');

После запуска описанной выше процедуры я получаю текстовую строку, которая в основном является запросом. Я буду называть это далее "oneliner-output", просто для простоты.
'Oneline-output' выглядит следующим образом (я просто копирую / вставляю его из одной ячейки вывода, в которую я попал):

"SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac FROM dkj_p_k27ac"
  • Обратите внимание, что двойные кавычки с обеих сторон утверждения были частью myresult() выход. Я не добавил их.

Теперь я гораздо лучше понимаю проблематичную идею мышления, заключающуюся в создании единой функции, которая одновременно создала бы "выходной поток" и выполнила бы его. Я могу скопировать / вставить 'oneliner-output' в новое окно запроса Postgres и выполнить его как обычный запрос, получая нужные столбцы и строки в моем окне вывода данных.
Я хотел бы, однако, автоматизировать этот шаг, чтобы избежать шага копирования / вставки. Есть ли способ в Postgres использовать text вывод ('oneliner-output'), который я получаю от myresult() функция, и выполнить это? Можно ли создать вторую функцию, которая получит вывод myresult() и использовать его для выполнения запроса?

Вдобавок к этому, хотя я знаю, что следующий сценарий (здесь ниже) работает и фактически выводит именно нужные столбцы и строки:

-- DEALLOCATE stmt1; -- use this line after the first time 'stmt1' was created
prepare stmt1 as SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac FROM dkj_p_k27ac;
execute stmt1;

  • Я думал, что, возможно, что-то вроде следующего скриптинга может сработать после правильной настройки? Не уверен, как, хотя.

    prepare stmt1 as THE_OUTPUT_OF_myresult();
execute stmt1;

Попытка с рекурсором

CREATE OR REPLACE FUNCTION show_mytable(ref refcursor) RETURNS refcursor AS $$
BEGIN
   OPEN ref FOR SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac FROM dkj_p_k27ac;   -- Open a cursor 
   RETURN ref;    -- Return the cursor to the caller
END;
$$ LANGUAGE plpgsql;

Вызов:

BEGIN;
SELECT show_mytable('roy');
FETCH ALL IN "roy";

Эта процедура на самом деле работает и выплевывает нужные столбцы и строки, и еще раз, я должен предоставить точный оператор SELECT.

Я в основном хотел бы иметь возможность и вместо этого предоставить в качестве выходных данных моего myresult() функция. Что-то вроде этого:

CREATE OR REPLACE FUNCTION show_mytable(ref refcursor) RETURNS refcursor AS $$
BEGIN
   OPEN ref FOR myresult();   -- Open a cursor 
   RETURN ref;    -- Return the cursor to the caller
END;
$$ LANGUAGE plpgsql;

Вызов:

BEGIN;
SELECT show_mytable('roy');
FETCH ALL IN "roy";
Источник

2 ответа

Решение

Трюк с PREPARE не работает, так как не принимает * текстовую строку * (значение) как CREATE FUNCTION делает, но действительное утверждение (код).

Для преобразования данных в исполняемый код необходимо использовать динамический SQL, т.е. EXECUTE в функции plpgsql или DO заявление. Это работает без проблем, пока тип возвращаемого значения не зависит от результата первой функции myresult(), В противном случае вы вернулись, чтобы поймать 22, как указано в моем предыдущем ответе:

Важная часть заключается в том, чтобы как-то объявить тип возвращаемого значения (в данном случае тип строки). Вы можете создать TABLE, TEMP TABLE или же TYPE с целью. Или вы можете использовать подготовленное заявление или рекурсор.

Решение с подготовленным заявлением

Вы были очень близки Недостающим элементом головоломки является подготовка сгенерированного запроса с использованием динамического SQL.

Функция, чтобы подготовить заявление динамически

Создайте эту функцию один раз. Это оптимизированная и безопасная версия вашей функции myresult():

CREATE OR REPLACE FUNCTION f_prep_query (_tbl regclass, _prefix text)
  RETURNS void AS 
$func$
BEGIN
   IF EXISTS (SELECT 1 FROM pg_prepared_statements WHERE name = 'stmt_dyn') THEN
      DEALLOCATE stmt_dyn;
   END IF;                 -- you my or may not need this safety check 

   EXECUTE (
     SELECT 'PREPARE stmt_dyn AS SELECT '
         || string_agg(quote_ident(attname), ',' ORDER BY attname)
         || ' FROM ' || _tbl
      FROM   pg_catalog.pg_attribute
      WHERE  attrelid = _tbl
      AND    attname LIKE _prefix || '%'
      AND    attnum > 0
      AND    NOT attisdropped
     );
END
$func$  LANGUAGE plpgsql;

я использую regclass для параметра имени таблицы _tbl чтобы сделать его однозначным и безопасным против SQLi. Подробности:

Информационная схема не включает столбец oid системных каталогов, поэтому я переключился на pg_catalog.pg_attribute вместо information_schema.columns, Это тоже быстрее. Есть плюсы и минусы для этого:

Если подготовлено заявление с именем stmt_dyn уже существовал, PREPARE поднимет исключение. Если это приемлемо, снимите флажок в системном представлении pg_prepared_statements и следующее DEALLOCATE,
Более сложные алгоритмы позволяют управлять множеством подготовленных операторов за сеанс, или принимать имя подготовленного оператора в качестве дополнительного параметра, или даже использовать MD5-хэш строки запроса в качестве имени, но это выходит за рамки этого вопроса.

Быть в курсе, что PREPARE работает за пределами объема транзакций, один раз PREPARE успешно, подготовленный оператор существует на протяжении всего сеанса. Если транзакция переноса прервана, PREPARE не влияет. ROLLBACK не может удалить подготовленные заявления.

Динамическое выполнение запроса

Два запроса, но только один звонок на сервер. И очень эффективный тоже.

SELECT f_prep_query('tbl'::regclass, 'pre'::text);
EXECUTE stmt_dyn;

Проще и намного эффективнее для большинства простых случаев использования, чем создание временной таблицы или курсора и выбор / выборка из этого (что может быть другими вариантами).

SQL Fiddle.

Источник

Я думаю, что я тоже нашел решение, используя рекурсор.
Я был бы очень рад, если бы вы могли пройти через это, проверить и сказать мне, если вы думаете, что это "кошерный". Честно говоря, я не очень уверен, что я здесь придумал, так как я не очень знаком с синтаксисом. Но я скорее смог синтезировать это, используя различные примеры, которые я нашел в сети. Кажется, это работает для меня. Я был бы очень рад, если бы вы могли сформулировать это решение для меня и для других пользователей - и рассказать, что вы об этом думаете.

Сначала давайте создадим функцию, которая создает динамический SELECT заявление:

CREATE OR REPLACE FUNCTION myresult2()
  RETURNS text AS 
$func$
DECLARE
   myoneliner text;
   mytable    text := 'dkj_p_k27ac';
   myprefix   text := 'enri';
BEGIN
   SELECT INTO myoneliner  
          'SELECT '
        || string_agg(quote_ident(column_name::text), ',' ORDER BY column_name)
        || ' FROM ' || quote_ident(mytable)
   FROM   information_schema.columns
   WHERE  table_name = mytable
   AND    column_name LIKE myprefix||'%'
   AND    table_schema = 'public';  -- schema name; might be another param

   -- RAISE NOTICE 'My additional text: %', myoneliner; -- for debugging
   RETURN myoneliner;
END
$func$ LANGUAGE plpgsql;

Теперь давайте создадим вторую функцию, которая может выполнять вывод строки TEXT первой функции. myresult2():

CREATE OR REPLACE FUNCTION show_mytable(ref refcursor)
  RETURNS refcursor AS
$func$
DECLARE
   mydynamicstatment text := myresult2();
BEGIN       
   OPEN ref FOR EXECUTE mydynamicstatment;
   RETURN ref;  -- return cursor to the caller
END;
$func$ LANGUAGE plpgsql;

Вызов:

BEGIN;
SELECT show_mytable('roy');
FETCH ALL IN "roy";
Источник
Другие вопросы по тегам