Как выполнить строковый результат хранимой процедуры в postgres

Я создал следующую хранимую процедуру, которая в основном получает имя таблицы и префикс. Затем функция находит все столбцы, которые имеют этот префикс, и возвращает в качестве вывода команду запроса "выбрать" ("myoneliner"). следующее:

CREATE OR REPLACE FUNCTION mytext (mytable text, myprefix text)
RETURNS text AS $myoneliner$
declare
    myoneliner text;
BEGIN
   SELECT 'SELECT ' || substr(cols,2,length(cols)-2) ||' FROM '||mytable 
   INTO myoneliner  
     FROM (
        SELECT array(
           SELECT DISTINCT quote_ident(column_name::text)
           FROM   information_schema.columns
           WHERE  table_name = mytable
           AND    column_name LIKE myprefix||'%'
           order by quote_ident             
      )::text cols 
     ) sub;
   RETURN myoneliner;
END;
$myoneliner$ LANGUAGE plpgsql;

Вызов:

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

В результате выполнения этой хранимой процедуры и "select", которое следует за ней, я получаю следующий вывод в окне "Вывод данных" (все в одной ячейке с именем "mytext text"):

'SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac
 FROM dkj_p_k27ac'

Я хотел бы в основном иметь возможность взять командную строку вывода, которую я получил в качестве вывода и выполнить ее. Другими словами, я хотел бы иметь возможность и выполнить вывод моей хранимой процедуры. Как я могу это сделать?

Я попробовал следующее:

CREATE OR REPLACE FUNCTION mytext (mytable text, myprefix text)
RETURNS SETOF RECORD AS $$
declare
        smalltext text;
    myoneliner text;
BEGIN
   SELECT 'SELECT ' || substr(cols,2,length(cols)-2) ||' FROM '||mytable 
   INTO myoneliner  
     FROM (
        SELECT array(
           SELECT DISTINCT quote_ident(column_name::text)
           FROM   information_schema.columns
           WHERE  table_name = mytable
           AND    column_name LIKE myprefix||'%'
           order by quote_ident             
      )::text cols 
     ) sub;

   smalltext=lower(myoneliner);
   raise notice '%','my additional text '||smalltext;
   RETURN QUERY EXECUTE smalltext;
END;
$$ LANGUAGE plpgsql;

Функция вызова:

SELECT * from mytext('dkj_p_k27ac','enri');

Но я получаю следующее сообщение об ошибке, не могли бы вы порекомендовать, что я должен изменить, чтобы он выполнялся?:

ERROR:  a column definition list is required for functions returning "record"
LINE 26: SELECT * from mytext('dkj_p_k27ac','enri');

********** Error **********

ERROR: a column definition list is required for functions returning "record"
SQL state: 42601
Character: 728
Источник

1 ответ

Решение

Ваша первая проблема была решена с помощью динамического SQL с EXECUTE как советовал Крейг. Но кроличья нора идет глубже

CREATE OR REPLACE FUNCTION myresult(mytable text, myprefix text)
  RETURNS SETOF RECORD AS
$func$
DECLARE
   smalltext  text;
   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

   smalltext := lower(myoneliner);  -- nonsense
   RAISE NOTICE 'My additional text: %', myoneliner;

   RETURN QUERY EXECUTE myoneliner;
END
$func$ LANGUAGE plpgsql;

Основные моменты

  • Не переводите все утверждение в нижний регистр. Имена столбцов могут быть заключены в двойные кавычки с заглавными буквами, которые в данном случае чувствительны к регистру (без каламбура).

  • Вам не нужно DISTINCT в запросе на information_schema.columns, Имена столбцов уникальны для каждой таблицы.

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

  • Вы должны очистить все идентификаторы в динамическом коде, включая имена таблиц: quote_ident(mytable), Помните, что ваш текстовый параметр функции чувствителен к регистру! Запрос на information_schema.columns требует этого тоже.

  • Я распутал всю вашу конструкцию, чтобы построить список имен столбцов с string_agg() вместо конструктора массива. Соответствующий ответ:

  • Оператор присваивания в plpgsql := ,

  • Упрощенный синтаксис RAISE NOTICE,

Основная проблема, которую невозможно решить

Все это по-прежнему не решает вашу главную проблему: SQL требует определения столбцов, которые должны быть возвращены. Вы можете обойти это, возвращая анонимные записи, как вы пытались. Но это просто откладывает неизбежное. Теперь вы должны предоставить список определений столбцов во время вызова, точно так же, как сообщает ваше сообщение об ошибке. Но вы просто не знаете, какие столбцы будут возвращены. Словить 22.

Ваш звонок будет работать так:

SELECT *
FROM   myresult('dkj_p_k27ac','enri') AS f (
  enrich_d_dkj_p_k27ac text  -- replace with actual column types
, enrich_lr_dkj_p_k27ac text
, enrich_r_dkj_p_k27ac text);

Но вы не знаете число, имена (необязательно) и типы данных возвращаемых столбцов не во время создания функции и даже во время вызова. Это невозможно сделать за один звонок. Вам нужно два отдельных запроса к базе данных.

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

Источник
Другие вопросы по тегам