Базовая процедура Postgres

Для разработки базовой процедуры приведена таблица с базовыми скалярными типами Postgres и соответствующими внутренними типами платформы:

Базовый скалярный тип Postgres	Псевдонимы для типа Postgres	Внутренний тип платформы
smallint	int2	bigint
integer	int int4	bigint
bigint	int8	bigint
numeric[(p, s)]	decimal[(p, s)]	text
real	float4	double
double precision	float8	double
boolean	bool	bigint
bytea	-	bytea
character[(n)]	char[(n)]	text
character varying [(n)]	varchar[(n)]	text
text	-	text
date	-	text
timestamp [(p)] without time zone	timestamptz	text

Подробное описание типов доступно в документации Postgres.

Базовый композитный и табличный тип

По стандартному языку базы данных создаются типы, таблицы и процедуры.

Базовый композитный тип – это тип, который состоит из базовых скалярных типов. Внутри базового композитного типа не может быть другого композитного типа. Важно не путать композитный тип с типом ROWTYPE или типом RECORD. Базовый композитный тип должен быть явно объявлен с помощью конструкции CREATE TYPE:

CREATE TYPE <наименование типа> AS (
<имя столбца> <тип столбца>
);

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

CREATE FUNCTION <наименование функции>(<входные аргументы>) RETURNS SETOF <наименование созданного типа> <код процедуры>;

Важно. Другие синтаксические конструкции для объявления входных и выходных табличных параметров недопустимы.

Пример

Структура таблицы, получаемая на выходе процедур:

id (int). Идентификатор;
name (varchar). Название фрукта.

Создание таблицы:

CREATE TABLE fruit (
id SERIAL PRIMARY KEY,
name VARCHAR(200) NOT Null
);

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

CREATE FUNCTION fruits_take_all()
  RETURNS SETOF fruit
LANGUAGE plpgsql
AS $$
DECLARE
    ups_row fruit;
    BEGIN
        RETURN QUERY SELECT * FROM fruit ORDER BY id;
    END
$$;
SELECT * FROM fruits_take_all();

Процедура, принимающая входной скалярный параметр «id» типа integer, извлекает строку из таблицы фруктов:

Создание параметра:

CREATE TYPE fruit_id AS (id INTEGER);

Процедура:

CREATE FUNCTION fruit_get(fruit_id INTEGER)
RETURNS SETOF fruit
LANGUAGE plpgsql
AS $$
    BEGIN
        RETURN QUERY SELECT * FROM fruit WHERE id = fruit_id;
    END
$$;
SELECT * FROM fruit_get(fruit_id := 13);

См. также:

Настройка интеграции с табличными источниками данных | Базовая процедура 1С