Как получить набор результатов из хранимой процедуры postgresql?

Active learning: our own return value function

Let’s have a go at writing our own functions featuring return values.

1. First of all, make a local copy of the function-library.html file from GitHub. This is a simple HTML page containing a text field and a paragraph. There’s also a element, in which we have stored a reference to both HTML elements in two variables. This little page will allow you to enter a number into the text box, and display different numbers related to it in the paragraph below.
2. Let’s add some useful functions to this element. Below the two existing lines of JavaScript, add the following function definitions:
   The and functions are fairly obvious — they return the square or cube of the number that was given as a parameter. The function returns the factorial of the given number.
3. Next, we’re going to include a way to print out information about the number entered into the text input. Enter the following event handler below the existing functions:
   Here we are adding a listener to the event. It runs whenever the event fires on the text input — that is, when a new value is entered into the text , and submitted (e.g., enter a value, then unfocus the input by pressing Tab or Return). When this anonymous function runs, the value in the is stored in the constant.
   Next, we do a conditional test. If the entered value is not a number, an error message is printed to the paragraph. The test looks at whether the expression returns . The function to test whether the value is not a number — if so, it returns , and if not, it returns .
   If the test returns , the value is a number. Therefore, a sentence is printed out inside the paragraph element that states the square, cube, and factorial values of the number. The sentence calls the , , and functions to calculate the required values.
4. Save your code, load it in a browser, and try it out.

Note: If you have trouble getting the example to work, feel free to check your code against the finished version on GitHub (see it running live also), or ask us for help.

Образец функции с табличным значением

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

Пример кода

Объявление и использование образца функции с табличным значением

После компиляции образца возвращающей табличное значение функции его можно объявить в Transact-SQL следующим образом.

Выполнение объектов базы данных, написанных на языке Visual C++ и скомпилированных с параметром /clr:pure, не поддерживается в SQL Server 2005 (9.x). Например, в число таких объектов базы данных входят функции с табличным значением.

Чтобы проверить образец, выполните следующий код Transact-SQL.

2 ответа

Лучший ответ

Уловка с не работает , поскольку он принимает не * текстовую строку * (значение), как , а действительный оператор (код).

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

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

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

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

Вы были очень близки. Отсутствует часть головоломки — подготовить сгенерированный запрос с помощью динамического SQL .

Функция для динамической подготовки оператора

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

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

Имя таблицы как параметр функции PostgreSQL

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

Как проверить, существует ли таблица в данной схеме

Если подготовленный оператор с именем уже существует, вызовет исключение. Если это приемлемо, снимите флажок в системном представлении и следующие . _{Возможны более сложные алгоритмы для управления несколькими подготовленными операторами за сеанс или использования имени подготовленного оператора в качестве дополнительного параметра или даже использования хеша MD5 строки запроса в качестве имени, но это выходит за рамки этого вопроса.}

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

Выполнение динамического запроса

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

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

2

Community
20 Июн 2020 в 09:12

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

Теперь давайте создадим вторую функцию, которая может выполнять строковый ТЕКСТ-вывод первой функции :

Вызов:

1

Erwin Brandstetter
8 Янв 2015 в 01:03

Differences Between Transact-SQL and CLR Table-Valued Functions

Transact-SQL table-valued functions materialize the results of calling the function into an intermediate table. Since they use an intermediate table, they can support constraints and unique indexes over the results. These features can be extremely useful when large results are returned.

In contrast, CLR table-valued functions represent a streaming alternative. There is no requirement that the entire set of results be materialized in a single table. The IEnumerable object returned by the managed function is directly called by the execution plan of the query that calls the table-valued function, and the results are consumed in an incremental manner. This streaming model ensures that results can be consumed immediately after the first row is available, instead of waiting for the entire table to be populated. It is also a better alternative if you have very large numbers of rows returned, because they do not have to be materialized in memory as a whole. For example, a managed table-valued function could be used to parse a text file and return each line as a row.

Получение данных. Команда Select

Последнее обновление: 20.03.2018

Для извлечения данных из БД применяется команда SELECT. В упрощенном виде она имеет следующий синтаксис:

SELECT список_столбцов FROM имя_таблицы

Например, пусть ранее была создана таблица Products, и в нее добавлены некоторые начальные данные:

CREATE TABLE Products
(
	Id SERIAL PRIMARY KEY,
	ProductName VARCHAR(30) NOT NULL,
	Manufacturer VARCHAR(20) NOT NULL,
	ProductCount INTEGER DEFAULT 0,
	Price NUMERIC
);

INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price)
VALUES
('iPhone X', 'Apple', 3, 36000),
('iPhone 8', 'Apple', 2, 41000),
('Galaxy S9', 'Samsung', 2, 46000),
('Galaxy S8 Plus', 'Samsung', 1, 56000),
('Desire 12', 'HTC', 5, 28000);

Получим все объекты из этой таблицы:

SELECT * FROM Products;

Символ звездочка * указывает, что нам надо получить все столбцы.

Однако использование символа звездочки * считается не очень хорошей практикой, так как, как правило, не все столбцы бывают нужны. И более
оптимальный подход заключается в указании всех необходимых столбцов после слова SELECT. Исключение составляет тот случай, когда надо получить данные по абсолютно всем столбцам таблицы.
Также использование символа * может быть предпочтительно в таких ситуациях, когда в точности не известны названия столбцов.

Если нам надо получить данные не по всем, а по каким-то конкретным столбцам, то тогда все эти спецификации столбцов перечисляются через запятую после SELECT:

SELECT ProductName, Price FROM Products;

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

SELECT ProductCount, Manufacturer, Price * ProductCount
FROM Products;

Здесь при выборке будут создаваться три столбца. Причем третий столбец представляет значение столбца Price, умноженное на значение столбца
ProductCount, то есть совокупную стоимость товара.

С помощью оператора AS можно изменить название выходного столбца или определить его псевдоним:

SELECT ProductCount AS Title, 
Manufacturer, 
Price * ProductCount  AS TotalSum
FROM Products;

В данном случае результатом выборки являются данные по 3-м столбцам. Для первого столбца определяется псевдоним Title, хотя в реальности
он будет представлять столбец ProductName. Второй столбец сохраняет свое название — Manufacturer. Третий столбец TotalSum хранит произведение столбцов
ProductCount и Price.

НазадВперед

What are return values?

Return values are just what they sound like —  the values that a function returns when it has completed. You’ve already met return values a number of times, although you may not have thought about them explicitly.

Let’s return to a familiar example (from a in this series):

The function is invoked on the string, and is passed two parameters:

1. the substring to find (‘cold’).
2. the string to replace it with (‘warm’).

When the function completes (finishes running), it returns a value, which is a new string with the replacement made. In the code above, the result of this return value is saved in the variable .

If you look at the function MDN reference page, you’ll see a section called . It is very useful to know and understand what values are returned by functions, so we try to include this information wherever possible.

Some functions don’t return any value. (In these cases, our reference pages list the return value as or .) For example, in the function we built in the previous article, no specific value is returned when the function is invoked. It just makes a box appear somewhere on the screen — that’s it!

Generally, a return value is used where the function is an intermediate step in a calculation of some kind. You want to get to a final result, which involves some values that need to be calculated by a function. After the function calculates the value, it can return the result so it can be stored in a variable; and you can use this variable in the next stage of the calculation.

To return a value from a custom function, you need to use the return keyword. We saw this in action recently in our random-canvas-circles.html example. Our function draws 100 random circles somewhere on an HTML :

Inside each loop iteration, three calls are made to the function, to generate a random value for the current circle’s x-coordinate, y-coordinate, and radius, respectively. The function takes one parameter — a whole number — and it returns a whole random number between and that number. It looks like this:

This could be written as follows:

But the first version is quicker to write, and more compact.

We are returning the result of the calculation each time the function is called. This return value appears at the point the function was called, and the code continues.

So when you execute the following:

If the three calls returned the values , , and , respectively, the line would actually be run as if it were this:

The function calls on the line are run first, and their return values substituted for the function calls, before the line itself is then executed.

Возврат сложных типов данных¶

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

Есть пользовательский данных, который хранит информацию о клиентах.

CREATE TYPE t_customer AS (
  id int,
  name varchar
);

Есть таблица, которая хранит информацию о клиентах

CREATE TABLE customers(
    id serial PRIMARY KEY,
    name varchar NOT NULL UNIQUE
);

db=# INSERT INTO customers(name) VALUES('name1');
INSERT 0 1

db=# INSERT INTO customers(name) VALUES('name2');
INSERT 0 1

Возврат пользовательских типов данных

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

CREATE OR REPLACE FUNCTION return_t_customers() RETURNS SETOF t_customer AS
$$
DECLARE
  _result t_customer;
BEGIN
  FOR _result.id, _result.name IN SELECT id, name FROM customers
  LOOP
    RETURN NEXT _result;
  END LOOP;
  RETURN;
END
$$ LANGUAGE plpgsql;

db=# SELECT * FROM return_t_customers();
 id | name
----+-------
  1 | name1
  2 | name2
(2 rows)

Предупреждение

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

Пример

FOR _result IN SELECT * FROM customers --
LOOP
  RETURN NEXT _result;
END LOOP;
RETURN;

Выше приведенное присваивание эквивалентно следующему псевдокоду

FOR _result1], _result2 IN SELECT customers1], customers2 FROM customers --

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

1 ответ

Решение

Использование :

Вызов:

Объяснение:

Намного практичнее явно определить тип возвращаемого значения, чем просто объявить его как запись. Таким образом, вам не нужно предоставлять список определений столбцов при каждом вызове функции. это один из способов сделать это. Есть и другие. Типы данных параметры должны точно соответствовать тому, что возвращается запросом.

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

Я сделал это для всех столбцов в моем примере.
Но обратите внимание на потенциальный конфликт имен параметр и псевдоним столбца с тем же именем. В данном конкретном случае () Postgres использует псевдоним столбца над параметр в любом случае

Это может быть неоднозначным в других контекстах, однако. Существуют различные способы избежать путаницы:
Используйте порядковый номер элемента в списке SELECT: , Пример:
Повторите выражение ,
(Не применимо здесь.) Установите параметр конфигурации или используйте специальную команду на функцию. Пример:

Не используйте «текст» и «считать» в качестве имен столбцов. И то, и другое разрешено использовать в Postgres, но «count» — это зарезервированное слово в стандартном SQL, а базовое имя функции и «text» — базовый тип данных. Может привести к запутанным ошибкам. я использую а также в моих примерах.

Добавлен недостающий и исправил синтаксическую ошибку в заголовке. не — введите после имени.

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

альтернатива

Вот как я думаю, ваш запрос на самом деле должен выглядеть (вычисляя относительную долю на каждый токен):

Выражение это оконная функция. Вы можете использовать CTE вместо подзапроса — довольно, но подзапрос, как правило, дешевле в таких простых случаях, как этот.

Последний (но разрешено) при работе с параметры или (что делает неявное использование параметры).

с двумя параметрами работает только для типы. в подзапросе производит результат и через это производит результат, таким образом, мы имеем дело с номер автоматически и все просто становится на свои места.

156

2011-10-30 15:50

Привет, пожалуйста, проверьте ссылку ниже

EX:

3

2019-06-26 10:09

2 ответа

Лучший ответ

Если вы хотите создать функцию, возвращающую setof record, вам нужно будет определить типы столбцов в вашем операторе select

Ваш запрос должен выглядеть примерно так:

(вам, вероятно, потребуется изменить типы данных)

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

Например:

36

Community
23 Май 2017 в 12:25

Вернуть выбранные столбцы

Вызов:

У вас был , но вы не использовали переменную. Я удалил хлам.

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

Если пользователь не , по умолчанию используется простой . Это также (безопасное) значение по умолчанию, если второй параметр опущен — что возможно только после указания этого значения по умолчанию с помощью в определении функции.

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

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

Определите тип возврата функции (например, продемонстрировал @ertx), или вы должны предоставить список определений столбцов при каждом вызове функции, что неудобно.

Создание типа для этой цели (например, предложенного @ertx) является допустимым подходом, но, вероятно, излишним для одной функции. Так было в старых версиях Postgres до появления для этой цели — как показано выше.

Для этой простой функции вам не нужен цикл .

Каждой функции требуется объявление языка. в этом случае.

Я использую () вместо (), что является нормальным значением по умолчанию. Видеть:

Игнорирование часовых поясов в Rails и PostgreSQL

Возврат (набор) целых строк

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

Вернуть всю строку плюс произвольное добавление

Чтобы ответить на вопрос, добавленный TheRealChx101 в комментарии ниже:

Не так просто, но выполнимо. Мы можем отправить весь тип строки как одно поле и добавить больше:

«Магия» заключается в вызове функции, где мы (необязательно) декомпозируем тип строки:

db fiddle здесь (показаны все)

Если вам нужно что-то более «динамичное», подумайте:

Рефакторинг функции PL / pgSQL для возврата результатов различных запросов SELECT

51

Erwin Brandstetter
18 Авг 2020 в 13:09

Table-Valued Parameters

Table-valued parameters are user-defined table types that are passed into a procedure or function and provide an efficient way to pass multiple rows of data to the server. Table-valued parameters provide similar functionality to parameter arrays, but offer greater flexibility and closer integration with Transact-SQL. They also provide the potential for better performance. Table-valued parameters also help reduce the number of round trips to the server. Instead of sending multiple requests to the server, such as with a list of scalar parameters, data can be sent to the server as a table-valued parameter. A user-defined table type cannot be passed as a table-valued parameter to, or be returned from, a managed stored procedure or function executing in the SQL Server process. For more information about table-valued parameters, see Use Table-Valued Parameters (Database Engine).

Использование курсоров¶

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

CREATE OR REPLACE FUNCTION return_cursor() RETURNS refcursor AS
$$
DECLARE
  _result CONSTANT refcursor := '_result';
BEGIN
  OPEN _result FOR SELECT * FROM customers;
  RETURN _result;
END
$$ LANGUAGE plpgsql;

Для получения данных необходимо явным образом создавать транзакцию, так как курсоры существуют только в рамках одной транзакции.
Запрос FETCH ALL FROM <cursor_name> возвращает все данные курсора;

db=# BEGIN;
BEGIN

db=# SELECT * FROM return_cursor();
 return_cursor
---------------
 _result
(1 row)

db=# FETCH ALL FROM _result;
 id | name
----+-------
  1 | name1
  2 | name2
(2 rows)

db=# COMMIT;
COMMIT

У этого способа есть два приятных бонуса:

Рекомендации по созданию и использованию рекурсивных обобщенных табличных выражений

Следующие рекомендации применимы к определению рекурсивных обобщенных табличных выражений.

• Определение рекурсивного обобщенного табличного выражения должно содержать по крайней мере два определения обобщенного табличного выражения запросов — закрепленный элемент и рекурсивный элемент. Может быть определено несколько закрепленных элементов и рекурсивных элементов, однако все определения запросов закрепленного элемента должны быть поставлены перед первым определением рекурсивного элемента. Все определения обобщенных табличных выражений запросов (ОТВ) являются закрепленными элементами, если только они не ссылаются на само ОТВ.

  

  

  

  

  

  

  

  

  

• Закрепленные элементы должны объединяться одним из следующих операторов над множествами: UNION ALL, UNION, INTERSECT или EXCEPT. UNION ALL является единственным оператором над множествами, который может находиться между последним закрепленным элементом и первым рекурсивным элементом, а также может применяться при объединении нескольких рекурсивных элементов.

• Количество столбцов членов указателя и рекурсивных элементов должно совпадать.

• Тип данных столбца в рекурсивном элементе должен совпадать с типом данных соответствующего столбца в закрепленном элементе.

• Предложение FROM рекурсивного элемента должно ссылаться на обобщенное табличное выражение expression_name только один раз.

• Следующие элементы недопустимы в определении CTE_query_definition рекурсивного элемента:

  • (Если уровень совместимости базы данных имеет значение 110 или больше. См. раздел Критические изменения в функциях компонента ядра СУБД в SQL Server 2016).

  • Скалярное агрегирование

  • , , ( допускается)

  • Вложенные запросы

  • Указание, применимое к рекурсивной ссылке на обобщенное табличное выражение в определении CTE_query_definition.

Следующие рекомендации применимы к использованию рекурсивных обобщенных табличных выражений.

• Все столбцы, возвращаемые рекурсивным обобщенным табличным выражением, могут содержать значения NULL, независимо от того, могут ли иметь значения NULL столбцы, возвращаемые участвующими инструкциями .

• Неправильно составленное рекурсивное ОТВ может привести к бесконечному циклу. Например, если определение запроса рекурсивного элемента возвращает одинаковые значения как для родительского, так и для дочернего столбца, то образуется бесконечный цикл. Для предотвращения бесконечного цикла можно ограничить количество уровней рекурсии, допустимых для определенной инструкции, при помощи указания и значения в диапазоне от 0 до 32 767 в предложении OPTION инструкции , , или . Это дает возможность контролировать выполнение инструкции до тех пор, пока не будет разрешена проблема с кодом, из-за которой происходит зацикливание программы. Серверное значение по умолчанию равно 100. Если указано значение 0, ограничения не применяются. В одной инструкции может быть указан только одно значение . Дополнительные сведения см. в разделе Указания запросов (Transact-SQL).

• Представление, содержащее рекурсивное обобщенное табличное выражение, не может использоваться для обновления данных.

• Курсоры могут определяться на запросах при помощи обобщенных табличных выражений. Обобщенное табличное выражение является аргументом select_statement, который определяет результирующий набор курсора. Для рекурсивных обобщенных табличных выражений допустимы только однонаправленные и статические курсоры (курсоры моментального снимка). Если в рекурсивном обобщенном табличном выражении указан курсор другого типа, тип курсора преобразуется в статический.

• В обобщенном табличном выражении могут быть ссылки на таблицы, находящиеся на удаленных серверах. Если на удаленный сервер имеются ссылки в рекурсивном элементе обобщенного табличного выражения, создается буфер для каждой удаленной таблицы, так что к таблицам может многократно осуществляться локальный доступ. Если это запрос обобщенного табличного выражения, Index Spool/Lazy Spools отображается в плане запроса и будет иметь дополнительный предикат . Это один из способов подтверждения надлежащей рекурсии.

• Аналитические и агрегатные функции в рекурсивной части обобщенных табличных выражений применяются для задания текущего уровня рекурсии, а не для задания обобщенных табличных выражений. Такие функции, как , работают только с подмножествами данных, которые передаются им текущим уровнем рекурсии, но не со всем множеством данных, которые передаются в рекурсивную часть обобщенного табличного выражения. Дополнительные сведения см. в примере «Л. Использование аналитических функций в рекурсивном ОТВ» ниже.

Now it’s your turn!

At this point, we’d like you to have a go at writing out a couple of functions of your own and adding them to the library. How about the square or cube root of the number? Or the circumference of a circle with a given radius?

Some extra function related tips:

• Look at another example of writing error handling into functions. It is generally a good idea to check that any necessary parameters are validated, and that any optional parameters have some kind of default value provided. This way, your program will be less likely to throw errors.
• Think about the idea of creating a function library. As you go further into your programming career, you’ll start doing the same kinds of things over and over again. It is a good idea to create your own library of utility functions to do these sorts of things. You can copy them over to new code, or even just apply it to HTML pages wherever you need it.

Различия между функциями Transact-SQL и среды CLR с табличным значением

Возвращающие табличное значение функции Transact-SQL материализуют результаты вызова в промежуточной таблице. По этой причине они поддерживают в результатах ограничения и уникальные индексы. Это исключительно полезно при возвращении результатов большого объема.

Функции CLR с табличным значением, напротив, представляют альтернативу в виде потока. Материализация всего результирующего набора в одной таблице не требуется. Объект IEnumerable , возвращаемый управляемой функцией, напрямую вызывается планом выполнения запроса, который вызывает функцию, возвращающей табличное значение, и результаты используются в добавочном режиме. Такая потоковая модель обеспечивает немедленную обработку результатов сразу после получения первой строки, вместо того чтобы ожидать заполнения всей таблицы. Она также полезна, если возвращается очень большое число строк, поскольку таблица не материализуется в памяти как единое целое. Например, функция с табличным значением может использоваться для синтаксического анализа текстового файла и возвращения каждой строки текста в виде строки таблицы.