Как в PostgreSQL создать функцию, возвращающую табличные данные?

Всем привет! Сегодня мы с Вами рассмотрим примеры создания табличных функций, т.е. функций, которые возвращают табличные данные, в PostgreSQL на языке SQL, а также на расширении языка SQL, которое используется в PostgreSQL, а именно PL/pgSQL.

Введение

Наверное, у многих, кто только начинает работать с PostgreSQL, будь то начинающие программисты, или уже опытные, которые переходят с других СУБД, например, с Microsoft SQL Server, может возникать небольшое недопонимание того, как создавать функции, возвращающие табличные данные, и это не удивительно, ведь в PostgreSQL вариантов создания таких функций много и какой использовать непонятно. Поэтому сейчас я покажу все популярные варианты создания табличных функций в PostgreSQL и прокомментирую каждый из них, и Вы уже будете понимать, какой вариант Вам подходит, ну или какой лучше использовать в той или иной ситуации.

Начать хотелось бы с того, что создать табличную функцию в PostgreSQL можно с использованием разных языков, самыми популярными, конечно же, являются стандартный SQL и его расширение PL/pgSQL.

Синтаксис создания табличных функций на этих языках отличается, поэтому, как было уже отмечено, мы будем рассматривать варианты создания табличных функций, как на языке SQL, так и на языке PL/pgSQL.

Исходные данные

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

Заметка! Как создать таблицу в PostgreSQL с помощью pgAdmin 4.

  
  -- Удаление функций
  DROP FUNCTION IF EXISTS "public"."products_view", "public"."buyer_orders_view";

  -- Удаление таблиц для пересоздания схемы данных
  DROP TABLE IF EXISTS orders_products, orders, products, buyers, product_types;

  -- Создание таблиц
  CREATE TABLE buyers
  (
	buyer_id   INT          NOT NULL,
	buyer_name VARCHAR(100) NOT NULL,
	birthday   DATE         NOT NULL
  );

  CREATE TABLE orders
  (
	order_id     INT            NOT NULL,
	buyer_id     INT            NOT NULL,
	order_number INT            NOT NULL,
	order_date   DATE           NOT NULL,
	order_summa  NUMERIC(18, 2) NOT NULL
  );

  CREATE TABLE product_types
  (
	type_id INT          NOT NULL,
	name    VARCHAR(100) NOT NULL
  );

  CREATE TABLE products
  (
	product_id   INT            NOT NULL,
	product_name VARCHAR(100)   NOT NULL,
	description  VARCHAR(200)   NULL,
	price        NUMERIC(18, 2) NULL,
	type_id      INT            NOT NULL
  );

  CREATE TABLE orders_products
  (
	order_id   INT NOT NULL,
	product_id INT NOT NULL
  );

  -- Добавление записей в таблицы
  INSERT INTO buyers (buyer_id, buyer_name, birthday)
	VALUES (753, 'Зайцев А.Е.', '1998.04.12'),
		   (832, 'Иванов И.И.', '1993.07.16'),
		   (991, 'Попова Е.В.', '2001.10.28'),
		   (1028, 'John Smith', '2000.05.03'),
		   (1109, 'Сергеев А.С.', '1986.10.12'),
		   (1177, 'Петров С.Л.', '2003.06.11'),
		   (1201, 'Андреев В.А.', '1980.09.27');
		   
  INSERT INTO orders (order_id, buyer_id, order_number, order_date, order_summa)
	VALUES (1459, 1201, 151, '2020.04.02', 12750),
		   (1567, 991, 259, '2020.04.17', 6700),
		   (1615, 832, 307, '2020.05.02', 21440),
		   (1646, 1109, 338, '2020.05.07', 15540),
		   (1660, 1201, 352, '2020.05.16', 2100),
		   (1708, 832, 400, '2020.05.21', 6700),
		   (1718, 1028, 410, '2020.05.21', 12300),
		   (1893, 832, 115, '2020.06.11', 4600),
		   (1923, 1109, 85, '2020.06.29', 23990),
		   (1959, 1201, 151, '2020.07.02', 1300),
		   (2052, 832, 244, '2020.07.30', 7900),
		   (2057, 991, 249, '2020.07.30', 16900),
		   (2106, 753, 298, '2020.08.08', 900),
		   (2146, 1201, 338, '2020.08.15', 2200),
		   (2181, 832, 373, '2020.08.23', 16740);

  INSERT INTO product_types (type_id, name)
	VALUES (1, 'Физический'),
		   (2, 'Цифровой');

  INSERT INTO products (product_id, product_name, description, price, type_id)
	VALUES (16, 'Процессор V5', '4-ядерный процессор, 3600 МГц', 12300, 1),
		   (29, 'Материнская плата R7Q', '2 слота DDR4, 1 слот PCI-E', 4600, 1),
		   (38, 'Клавиатура S939', 'Проводная, интерфейс USB', 1300, 1),
		   (47, 'Мышь N56', 'USB', 900, 1),
		   (60, 'Материнская плата ES20', '4 слота DDR4, 2 слота PCI-E', 6700, 1),
		   (71, 'Принтер 3075', 'Лазерный, 20 стр/мин (A4), USB', 3500, 1),
		   (83, 'Кулер для процессора D17', NULL, 450, 1),
		   (96, 'Процессор V7', '6-ядерный процессор, 3700 МГц', 15540, 1),
		   (108, 'Антивирусная программа', NULL, 1200, 2),
		   (125, 'Операционная система', NULL, 6700, 2);
		   
  INSERT INTO orders_products (order_id, product_id)
	VALUES (1459, 16), (1459, 83), (1567, 125), (1615, 29),
		   (1615, 96), (1615, 38), (1646, 96), (1660, 108),
		   (1660, 47), (1708, 60), (1718, 16), (1893, 29),
		   (1923, 60), (1923, 83), (1923, 96), (1923, 38),
		   (1959, 38), (2052, 108), (2052, 125), (2057, 16),
		   (2057, 29), (2106, 47), (2146, 38), (2146, 47),
		   (2181, 108), (2181, 96);

Создание табличных функций в PostgreSQL на SQL

Начнем мы с рассмотрения примеров создания табличных функций с использованием языка SQL. Такие функции обычно используются тогда, когда не требуется задействовать в них какую-то логику, например, условные конструкции, циклы, переменные, т.е. все то, что мы можем использовать, так сказать, в классическом программировании, например, на том же языке PL/pgSQL.

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

Заметка! Как подключиться к PostgreSQL с помощью Azure Data Studio.

Возвращение табличных данных через OUT параметры

Принцип работы табличных функций в PostgreSQL отличается, например, от того же Microsoft SQL Server, тем, что здесь OUT параметры могут формировать результирующий набор данных, т.е. каждый объявленный OUT параметр является столбцом в результирующей таблице, которую возвращает функция.

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

  
  DROP FUNCTION IF EXISTS "public"."products_view";

  -- Создание табличной функции
  CREATE FUNCTION "public"."products_view"
  (
    IN  _buyer_id    Int,
    OUT order_id     Int, 
    OUT product_id   Int, 
    OUT order_number Int,
    OUT order_date   Date,
    OUT product_name VarChar(100),
    OUT product_type VarChar(100),
    OUT price        Numeric(18,2)  
  )
  LANGUAGE 'sql'
  AS 
  $$

    SELECT
      O."order_id",
      P."product_id",
      O."order_number",
      O."order_date",
      P."product_name",
      PT."name" AS "product_type",
      P."price"
    FROM "public"."orders" O
    INNER JOIN "public"."orders_products" OP ON OP."order_id" = O."order_id"
    INNER JOIN "public"."products" P ON P."product_id" = OP."product_id"
    INNER JOIN "public"."product_types" PT ON PT."type_id" = P."type_id"
    WHERE O."buyer_id" = _buyer_id;
	
  $$;

  -- Пример вызова
  SELECT * FROM "public"."products_view"(1201);

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

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

Именно поэтому в нашем примере вернулась только одна строка, хотя запрос возвращает не одну строку, а много.

Заметка! Типы функций в языке T-SQL (Microsoft SQL Server).

Возвращение табличных данных через OUT параметры с RETURNS SETOF Record

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

  
  DROP FUNCTION IF EXISTS "public"."products_view";

  -- Создание табличной функции
  CREATE FUNCTION "public"."products_view"
  (
    IN  _buyer_id    Int,
    OUT order_id     Int, 
    OUT product_id   Int, 
    OUT order_number Int,
    OUT order_date   Date,
    OUT product_name VarChar(100),
    OUT product_type VarChar(100),
    OUT price        Numeric(18,2)  
  )
  RETURNS SETOF Record
  LANGUAGE 'sql'
  AS 
  $$

    SELECT
      O."order_id",
      P."product_id",
      O."order_number",
      O."order_date",
      P."product_name",
      PT."name" AS "product_type",
      P."price"
    FROM "public"."orders" O
    INNER JOIN "public"."orders_products" OP ON OP."order_id" = O."order_id"
    INNER JOIN "public"."products" P ON P."product_id" = OP."product_id"
    INNER JOIN "public"."product_types" PT ON PT."type_id" = P."type_id"
    WHERE O."buyer_id" = _buyer_id;
	
  $$;

  -- Пример вызова
  SELECT * FROM "public"."products_view"(1201);

Как видим, теперь функция возвращает нам не одну строку данных, а все данные, которые соответствуют запросу в функции.

Возвращение табличных данных через OUT параметры с RETURNS SETOF Type

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

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

  
  DROP FUNCTION IF EXISTS "public"."buyer_orders_view";

  -- Создание табличной функции
  CREATE FUNCTION "public"."buyer_orders_view"
  (
	_buyer_id Int
  )
  RETURNS SETOF Int
  LANGUAGE 'sql'
  AS 
  $$

    SELECT
      O."order_id"
    FROM "public"."orders" O
    WHERE O."buyer_id" = _buyer_id
	
  $$;

  -- Пример вызова
  SELECT * FROM "public"."buyer_orders_view"(1201);

В данном случае мы объявили всего один OUT параметр, указали RETURNS SETOF Int и написали соответствующую инструкцию SELECT.

Заметка! План выполнения запроса в Microsoft SQL Server – что это такое и для чего он нужен.

Возвращение табличных данных с помощью RETURNS SETOF Table

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

  
  DROP FUNCTION IF EXISTS "public"."buyer_orders_view";

  -- Создание табличной функции
  CREATE FUNCTION "public"."buyer_orders_view"
  (
    _buyer_id Int
  )
  RETURNS SETOF "public"."orders"
  LANGUAGE 'sql'
  AS 
  $$

    SELECT
      O.*
    FROM "public"."orders" O
    WHERE O."buyer_id" = _buyer_id;
	
  $$;

  -- Пример вызова
 SELECT * FROM "public"."buyer_orders_view"(1201);

В данном случае мы указали RETURNS SETOF «public».»orders» и написали запрос, возвращающий данные из этой таблицы. Никаких OUT параметров при этом мы не объявляли.

Возвращение табличных данных с помощью RETURNS SETOF Composite type

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

Заметка! Как создать составной тип данных с помощью pgAdmin 4 в PostgreSQL.

  
  DROP TYPE IF EXISTS t_products;

  -- Создание составного типа данных
  CREATE TYPE t_products AS 
  (
    "order_id"     Int, 
    "product_id"   Int, 
    "order_number" Int,
    "order_date"   Date,
    "product_name" VarChar(100),
    "product_type" VarChar(100),
    "price"        Numeric(18,2)  
  );

  DROP FUNCTION IF EXISTS "public"."products_view";

  -- Создание табличной функции
  CREATE FUNCTION "public"."products_view"
  (
	_buyer_id Int
  )
  RETURNS SETOF t_products
  LANGUAGE 'sql'
  AS 
  $$

    SELECT
      O."order_id",
      P."product_id",
      O."order_number",
      O."order_date",
      P."product_name",
      PT."name" AS "product_type",
      P."price"
    FROM "public"."orders" O
    INNER JOIN "public"."orders_products" OP ON OP."order_id" = O."order_id"
    INNER JOIN "public"."products" P ON P."product_id" = OP."product_id"
    INNER JOIN "public"."product_types" PT ON PT."type_id" = P."type_id"
    WHERE O."buyer_id" = _buyer_id;
	
  $$;

  -- Пример вызова
  SELECT * FROM "public"."products_view"(1201);

Здесь мы создали составной тип данных t_products, который имеет нужную нам структуру, и указали его в инструкции RETURNS SETOF t_products.

Классический вариант возвращения табличных данных на SQL (RETURNS TABLE)

Самым популярным и, наверное, самым правильным способом возвращения табличных данных в PostgreSQL является способ с использованием конструкции RETURNS TABLE, при которой мы объявляем структуру возвращаемых табличных данных.

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

  
  DROP FUNCTION IF EXISTS "public"."products_view";

  -- Создание табличной функции
  CREATE FUNCTION "public"."products_view"
  (
	_buyer_id Int
  )
  RETURNS TABLE
  (
    "order_id"     Int, 
    "product_id"   Int, 
    "order_number" Int,
    "order_date"   Date,
    "product_name" VarChar(100),
    "product_type" VarChar(100),
    "price"        Numeric(18,2)   
   ) 
  LANGUAGE 'sql'
  AS 
  $$

    SELECT
      O."order_id",
      P."product_id",
      O."order_number",
      O."order_date",
      P."product_name",
      PT."name" AS "product_type",
      P."price"
    FROM "public"."orders" O
    INNER JOIN "public"."orders_products" OP ON OP."order_id" = O."order_id"
    INNER JOIN "public"."products" P ON P."product_id" = OP."product_id"
    INNER JOIN "public"."product_types" PT ON PT."type_id" = P."type_id"
    WHERE O."buyer_id" = _buyer_id;
	
  $$;

  -- Пример вызова
  SELECT * FROM "public"."products_view"(1201);

Как видите, в инструкции RETURNS TABLE мы объявляем структуру табличных данных точно так же, как в инструкциях создания таблиц.

Заметка! Чем отличаются функции от хранимых процедур в T-SQL (Microsoft SQL Server).

Создание табличных функций в PostgreSQL на PL/pgSQL

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

Однако функции, возвращающие табличные данные на PL/pgSQL, создаются немного по-другому, поэтому эти способы мы рассмотрим отдельно.

Возвращение табличных данных с помощью RETURN QUERY (RETURNS TABLE)

Сразу стоит отметить, что способы объявления структуры возвращаемых табличных данных будут точно такие же как те, которые мы уже рассматривали выше в случае с функциями на SQL, например, использование OUT параметров с RETURNS SETOF Record или RETURNS TABLE. Поскольку это не относится к языку, на котором будет написана табличная функция.

Однако все же есть важное отличие, помните, в самом первом способе мы объявляли OUT параметры и не указывали инструкцию RETURNS, в результате мы получали просто одну строку из результирующего набора данных. В случае PL/pgSQL, если будут возвращаться много строк, это работать не будет, т.е. мы обязаны указать инструкцию RETURNS.

В случае SQL самый популярный и предпочтительный вариант я указал самым последним, здесь напротив, самый удобный я рассмотрю самым первым. Поскольку с глобальной точки зрения способов возврата табличных данных на PL/pgSQL всего два — это RETURN QUERY и RETURN NEXT. И именно RETURN QUERY является наиболее популярным и предпочтительным вариантом возвращения табличных данных.

Суть способа в том, что мы объявляем структуру возвращаемых табличных данных, любым способом будь то указание названия таблицы или определение структуры непосредственно в функции, а в теле функции мы используем инструкцию RETURN QUERY, после которой мы пишем запрос, формирующий возвращаемые табличные данные.

При этом мы, конечно же, указываем в определении функции, что функция написана с использованием языка PL/pgSQL, а в теле функции мы можем использовать любые конструкции языка PL/pgSQL.

  
  DROP FUNCTION IF EXISTS "public"."products_view";

  -- Создание табличной функции
  CREATE FUNCTION "public"."products_view"
  (
	_buyer_id Int
  )
  RETURNS TABLE
  (
    "order_id"     Int, 
    "product_id"   Int, 
    "order_number" Int,
    "order_date"   Date,
    "product_name" VarChar(100),
    "product_type" VarChar(100),
    "price"        Numeric(18,2)   
   ) 
  LANGUAGE 'plpgsql'
  AS 
  $$

  DECLARE
    _price Numeric(18,2);
  
  BEGIN

    IF _buyer_id > 1000 THEN
      _price := 500;
    END IF;

    RETURN QUERY
      SELECT
        O."order_id",
        P."product_id",
        O."order_number",
        O."order_date",
        P."product_name",
        PT."name" AS "product_type",
        P."price"
      FROM "public"."orders" O
      INNER JOIN "public"."orders_products" OP ON OP."order_id" = O."order_id"
      INNER JOIN "public"."products" P ON P."product_id" = OP."product_id"
      INNER JOIN "public"."product_types" PT ON PT."type_id" = P."type_id"
      WHERE O."buyer_id" = _buyer_id
         AND (P."price" >= _price OR _price IS NULL);
  
  END
  $$;

  -- Пример вызова
  SELECT * FROM "public"."products_view"(1201);

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

Заметка! Отличия OLTP баз данных от DWH.

Возвращение табличных данных с помощью RETURN NEXT (RETURNS TABLE)

Вторым способом возвращения табличных данных на PL/pgSQL является способ с использованием инструкции RETURN NEXT.

Суть данного способа в том, что данные в результирующую таблицу мы добавляем построчно с помощью инструкции RETURN NEXT.

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

Примечание! Если Вам не требуется перебирать данные и обрабатывать каждую строку в отдельности, т.е. можно обойтись без цикла, не нужно использовать RETURN NEXT и цикл для создания табличных функций, так как это будет значительно медленнее, чем если указать простой запрос SELECT в инструкции RETURN QUERY. Иными словами, если требуется вернуть простые табличные данные, используйте RETURN QUERY.

  
  DROP FUNCTION IF EXISTS "public"."products_view";

  -- Создание табличной функции
  CREATE FUNCTION "public"."products_view"
  (
    IN  _buyer_id    Int 
  )
  RETURNS TABLE
  (
    "order_id"     Int, 
    "product_id"   Int, 
    "order_number" Int,
    "order_date"   Date,
    "product_name" VarChar(100),
    "product_type" VarChar(100),
    "price"        Numeric(18,2)   
  ) 
  LANGUAGE 'plpgsql'
  AS 
  $$
  BEGIN

     FOR 
       order_id,
       product_id,
       order_number,
       order_date,
       product_name,
       product_type,
       price
     IN
     (
        SELECT
          O."order_id",
          P."product_id",
          O."order_number",
          O."order_date",
          P."product_name",
          PT."name" AS "product_type",
          P."price"
        FROM "public"."orders" O
        INNER JOIN "public"."orders_products" OP ON OP."order_id" = O."order_id"
        INNER JOIN "public"."products" P ON P."product_id" = OP."product_id"
        INNER JOIN "public"."product_types" PT ON PT."type_id" = P."type_id"
        WHERE O."buyer_id" = _buyer_id
     )
      LOOP

       RETURN NEXT;

      END LOOP;
	
      RETURN;
   
  END
  $$;

  -- Пример вызова
  SELECT * FROM "public"."products_view"(1201);

Как видите, в данном случае в FOR мы перечислили все названия столбцов из результирующей таблицы, затем в IN мы указали запрос получения данных, которые мы хотим обработать, и в LOOP мы можем обрабатывать каждую строку в отдельности, в данном случае мы ничего не делаем, а просто сразу указываем RETURN NEXT без какой-либо обработки. В завершении всей инструкции мы указываем RETURN.

Заметка! Что такое DDL, DML, DCL и TCL в языке SQL.

Возвращение табличных данных с помощью RETURN NEXT (RETURNS SETOF Record)

Этот и последующий способ является всего лишь вариацией использования инструкции RETURN NEXT.

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

Кроме этого, здесь показано, что в цикле FOR мы можем задействовать тип Record, а в LOOP мы уже присваиваем каждому столбцу результирующего набора соответствующее значение.

  
  DROP FUNCTION IF EXISTS "public"."products_view";

  -- Создание табличной функции
  CREATE FUNCTION "public"."products_view"
  (
    IN  _buyer_id    Int,
    OUT order_id     Int, 
    OUT product_id   Int, 
    OUT order_number Int,
    OUT order_date   Date,
    OUT product_name VarChar(100),
    OUT product_type VarChar(100),
    OUT price        Numeric(18,2)  
  )
  RETURNS SETOF Record
  LANGUAGE 'plpgsql'
  AS 
  $$
  DECLARE
      rec Record;
  BEGIN

     FOR rec IN
     (
        SELECT
          O."order_id",
          P."product_id",
          O."order_number",
          O."order_date",
          P."product_name",
          PT."name" AS "product_type",
          P."price"
        FROM "public"."orders" O
        INNER JOIN "public"."orders_products" OP ON OP."order_id" = O."order_id"
        INNER JOIN "public"."products" P ON P."product_id" = OP."product_id"
        INNER JOIN "public"."product_types" PT ON PT."type_id" = P."type_id"
        WHERE O."buyer_id" = _buyer_id
     )
      LOOP

        order_id     = rec.order_id;
        product_id   = rec.product_id;
        order_number = rec.order_number;
        order_date   = rec.order_date;
        product_name = rec.product_name;
        product_type = rec.product_type;
        price        = rec.price;

        RETURN NEXT;

      END LOOP;
	
      RETURN;
   
  END
  $$;

  -- Пример вызова
  SELECT * FROM "public"."products_view"(1201);

Возвращение табличных данных с помощью RETURN NEXT (RETURNS SETOF Composite type)

Здесь показано, что мы можем использовать и составной тип данных в RETURNS SETOF. Кроме этого, в данном случае в LOOP мы ничего не присваиваем, а просто сразу указываем RETURN NEXT rec.

  
  DROP TYPE IF EXISTS t_products;

  -- Создание составного типа данных
  CREATE TYPE t_products AS 
  (
    "order_id"     Int, 
    "product_id"   Int, 
    "order_number" Int,
    "order_date"   Date,
    "product_name" VarChar(100),
    "product_type" VarChar(100),
    "price"        Numeric(18,2)  
  );

 DROP FUNCTION IF EXISTS "public"."products_view";

  -- Создание табличной функции
  CREATE FUNCTION "public"."products_view"
  (
    IN  _buyer_id    Int 
  )
  RETURNS SETOF t_products
  LANGUAGE 'plpgsql'
  AS 
  $$
  DECLARE
    rec Record;
  BEGIN

     FOR rec IN
     (
        SELECT
          O."order_id",
          P."product_id",
          O."order_number",
          O."order_date",
          P."product_name",
          PT."name" AS "product_type",
          P."price"
        FROM "public"."orders" O
        INNER JOIN "public"."orders_products" OP ON OP."order_id" = O."order_id"
        INNER JOIN "public"."products" P ON P."product_id" = OP."product_id"
        INNER JOIN "public"."product_types" PT ON PT."type_id" = P."type_id"
        WHERE O."buyer_id" = _buyer_id
     )
      LOOP
          RETURN NEXT rec;
      END LOOP;
	
      RETURN;
   
  END
  $$;

  -- Пример вызова
  SELECT * FROM "public"."products_view"(1201);

Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL, в нем очень подробно рассмотрены основные конструкции языка.

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