Функция Postgres, возвращающая таблицу, не возвращает данные в столбцах

У меня есть функция Postgres, которая возвращает таблицу:

CREATE OR REPLACE FUNCTION testFunction() RETURNS TABLE(a int, b int) AS
$BODY$
DECLARE a int DEFAULT 0;
DECLARE b int DEFAULT 0;
BEGIN
CREATE TABLE tempTable AS SELECT a, b;
RETURN QUERY SELECT * FROM tempTable; 
DROP TABLE tempTable;
END;
$BODY$
LANGUAGE plpgsql;

Эта функция не возвращает данные в виде строк и столбцов. Вместо этого он возвращает данные как:

(0,0)

Это вызывает проблему в блоке cfquery Coldfusion при извлечении данных. Как получить данные в строках и столбцах, когда таблица возвращается из этой функции? Другими словами: почему функция PL/pgSQL не возвращает данные в виде столбцов?


postgresql sql plpgsql postgresql-9.1
person Satish Sharma    schedule 31.01.2013    source источник
comment
Почему вы используете временную таблицу? Это совершенно не нужно и замедляет работу без каких-либо преимуществ (и вы можете использовать простую функцию SQL, которая немного ускорит работу)   -  person a_horse_with_no_name    schedule 02.02.2013

Ответы (2)


arrow_upward
28
arrow_downward

Чтобы получить отдельные столбцы вместо типа строки, вызовите функцию с помощью:

SELECT * FROM testfunction();

Точно так же, как если бы вы выбрали все столбцы из таблицы.
Также рассмотрите эту проверенную форму вашей тестовой функции:

CREATE OR REPLACE FUNCTION testfunction()
  RETURNS TABLE(a int, b int)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _a int := 0;
   _b int := 0;
BEGIN
   CREATE TABLE tempTable AS SELECT _a, _b;
   RETURN QUERY SELECT * FROM tempTable;
   DROP TABLE tempTable;
END
$func$;

Особенно:

Ключевое слово DECLARE нужно только один раз.

Избегайте объявления параметров, которые уже (неявно) объявлены как параметры OUT в предложении RETURNS TABLE (...).

Не используйте идентификаторы CaMeL-case без кавычек в Postgres. Это работает, идентификаторы без кавычек приводятся к нижнему регистру, но это может привести к путанице. Видеть:

Временная таблица в примере совершенно бесполезна (вероятно, слишком упрощена). Приведенный пример сводится к следующему:

CREATE OR REPLACE FUNCTION testfunction(OUT a int, OUT b int)
  LANGUAGE plpgsql AS
$func$
BEGIN
   a := 0;
   b := 0;
END
$func$;
person Erwin Brandstetter    schedule 01.02.2013
comment
В документации PostgreSQL 12 содержится дополнительный RETURN;, чтобы четко указать, когда вы закончите сборку выходных данных. - person Jabro; 31.07.2020
comment
@JabroJacob: В руководстве также говорится (в указанном месте): This restriction does not apply to functions with output parameters and functions returning void, however. In those cases a RETURN statement is automatically executed if the top-level block finishes. Вы можете добавить RETURN, чтобы быть явным, но здесь это шум. - person Erwin Brandstetter; 31.07.2020
comment
Что делать, если в таблице больше 10 столбцов? - person TheRealChx101; 14.08.2020
comment
@TheRealChx101: Что означает число 10? - person Erwin Brandstetter; 21.08.2020
comment
Это просто случайное число, не имеющее значения. Я искал решение в случае, когда у вас было много столбцов. Я полагаю, что вы уже ответили на этот вопрос здесь: stackoverflow.com/a/8611675/1219213 - person TheRealChx101; 21.08.2020

arrow_upward
4
arrow_downward

Конечно, вы можете сделать это, поместив вызов функции в предложение FROM, как правильно ответил Эрик Брандштеттер. Однако это иногда усложняет запрос, в котором уже есть другие вещи в предложении FROM. Чтобы получить отдельные столбцы, которые возвращает функция, вы можете использовать этот синтаксис:

SELECT (testfunction()).*

Или получить только столбец с именем «a»:

SELECT (testfunction()).a

Поместите всю функцию, включая входные значения, в круглые скобки, а затем точку и имя нужного столбца или звездочку.

Чтобы получить имена столбцов, которые возвращает функция, вам нужно:

  • проверить исходный код
  • сначала проверьте результат функции, например: SELECT * FROM testfunction() .

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

CREATE FUNCTION funky(a integer, b integer)
RETURNS TABLE(x double precision, y double precision) AS $$
 SELECT a*random(), b*random();
$$ LANGUAGE SQL;

CREATE TABLE mytable(a integer, b integer);
INSERT INTO mytable
    SELECT generate_series(1,100), generate_series(101,200);

Вы можете вызвать функцию "funky(a,b)" без необходимости помещать ее в предложение FROM:

SELECT (funky(mytable.a, mytable.b)).*
FROM mytable;

Что приведет к 2 столбцам:

         x         |         y         
-------------------+-------------------
 0.202419687062502 |   55.417385618668
  1.97231830470264 |  63.3628275180236
  1.89781916560605 |  1.98870931006968
(...)
person wbloos    schedule 10.01.2020
comment
Развертывание результата set-return непосредственно в списке SELECT может показаться разумным сокращением. Но если функция возвращает несколько столбцов (как в данном случае), это приводит к тому, что функция вычисляется несколько раз. Более дорогой, может иметь непреднамеренные побочные эффекты и даже может давать удивительные результаты с нестабильными функциями. Как правило, ничего из этого вам не нужно. См.: stackoverflow.com/a/28853666/939860 и stackoverflow.com/a/18370271/939860. - person Erwin Brandstetter; 01.08.2020