Использование PL/pgSQL

Евгения Кузина

Обзор

PL/pgSQL — это загружаемый процедурный язык для СУБД ADPG/PostgreSQL.

SQL — язык, который ADPG/PostgreSQL и большинство других реляционных баз данных используют в качестве языка запросов. Каждый оператор SQL должен выполняться сервером базы данных индивидуально. Это означает, что клиентское приложение должно отправлять каждый запрос на сервер базы данных, ждать его обработки, получать и обрабатывать результаты, а затем отправлять дальнейшие запросы на сервер. Все это требует межпроцессного взаимодействия, а также влечет за собой значительную нагрузку на сеть.

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

PL/pgSQL имеет следующие преимущества:

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

  • Предоставляет структуры управления для языка SQL.

  • Позволяет выполнять сложные вычисления.

  • Наследует все определяемые пользователем типы, функции, процедуры и операторы.

  • Может быть определен как доверенный для сервера базы данных PostgreSQL.

  • Прост в использовании.

В ADPG расширение plpgsql, необходимое для использования PL/pgSQL, предустановлено в базе данных postgres. При создании новой базы данных устанавливаются расширения, присутствующие в базе данных template1. По умолчанию в template1 установлено расширение plpgsql. См. Template Databases.

Структура PL/pgSQL

PL/pgSQL поддерживает анонимные блоки кода, выполняемые с помощью оператора DO.

DO
$$
    DECLARE
        book_num integer;
    BEGIN
        SELECT count(*) into book_num FROM book;
        RAISE NOTICE 'The number of books: %', book_num;
END;
$$;

Если необходимо повторно использовать код, можно создать функцию или процедуру. Для этого используйте команду CREATE FUNCTION или CREATE PROCEDURE. В следующем примере определяется функция, написанная на PL/pgSQL. LANGUAGE указывает имя языка, на котором написана функция:

CREATE FUNCTION my_function(integer, text) RETURNS integer
AS 'function body text'
LANGUAGE plpgsql;

Тело функции представляет собой строковый литерал. Часто для записи тела функции используются долларовые кавычки ($$), а не обычный синтаксис одинарных кавычек (см. Dollar-quoted string constants). Без долларовых кавычек любые одинарные кавычки или обратную косую черту в теле функции необходимо экранировать, удвоив их.

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

[ <<<метка>>> ]
[ DECLARE
    <объявления> ]
BEGIN
    <операторы>
END [ <метка> ];

Каждое объявление и каждый оператор внутри блока завершаются точкой с запятой. Блок, расположенный внутри другого блока, должен иметь точку с запятой после END, как показано выше. Однако последний END, завершающий тело функции, не требует точки с запятой.

Метка необходима только в том случае, если вы хотите идентифицировать блок для использования в операторе EXIT или уточнить имена переменных, объявленных в блоке. Если после END указана метка, она должна совпадать с меткой в начале блока.

PL/pgSQL позволяет использовать те же комментарии, что и обычный SQL. Двойное тире (--) начинает однострочный комментарий, блочный комментарий начинается с /* и заканчивается */.

Любой оператор в разделе операторов блока может быть подблоком. Подблоки используются для логической группировки или для локализации переменных в небольшой группе операторов. Одноименные переменные, объявленные в подблоке, заменяют любые переменные внешних блоков, но вы можете получить доступ к внешним переменным, если уточните имена с помощью метки их блока. Например:

CREATE FUNCTION my_function() RETURNS integer AS $$
    << my_label >>
    DECLARE
        variable1 integer := 100;
    BEGIN
        RAISE NOTICE 'Variable1 is %', variable1; -- Выведет 100
        variable1 := 50;
        -- Создание подблока
    DECLARE
        variable1 integer := 200;
    BEGIN
        RAISE NOTICE 'Variable1 is %', variable1; -- Выведет 200
        RAISE NOTICE 'Variable1 is %', my_label.variable1; -- Выведет 50
    END;

    RAISE NOTICE 'Variable1 is %', variable1; -- Выведет 50

    RETURN variable1;
END;
$$ LANGUAGE plpgsql;


SELECT my_function();

Результат:

NOTICE:  Variable1 is 100
NOTICE:  Variable1 is 200
NOTICE:  Variable1 is 50
NOTICE:  Variable1 is 50
 my_function
-------------
          50

Важно не путать использование BEGIN/END для группировки операторов в PL/pgSQL с одноименными командами SQL для управления транзакциями. В PL/pgSQL BEGIN/END используются только для группировки. Они не начинают и не завершают транзакцию. Дополнительную информацию о том, как работать с транзакциями в PL/pgSQL, можно найти в статье Transaction management. Кроме того, блок с выражением EXCEPTION по сути создает вложенную транзакцию, откат которой можно выполнить, не затрагивая внешнюю транзакцию. Это описано в разделе Trapping errors.

Объявления

Все переменные, используемые в блоке, должны быть объявлены в разделе блока DECLARE. Единственным исключением является переменная цикла FOR. Переменные PL/pgSQL могут иметь любой тип данных SQL, например integer, varchar, char и т.д.

Примеры объявлений переменных:

account_id integer;
amount numeric(5);
article varchar;
my_row tablename%ROWTYPE;
my_field tablename.columnname%TYPE;
arow RECORD;

Общий синтаксис объявления переменных:

<имя> [ CONSTANT ] <тип> [ COLLATE <имя_правила_сортировки> ] [ NOT NULL ] [ { DEFAULT | := | = } <выражение> ];
Параметры объявления переменных

имя

Имя переменной

CONSTANT

Предотвращает изменение значения переменной после инициализации, таким образом значение остается постоянным в блоке

тип

Тип переменной

COLLATE <имя_правила_сортировки>

Определяет параметры сортировки, которые будут использоваться для переменной (см. Collation of PL/pgSQL variables)

NOT NULL

Если указано, присвоение значения NULL приведет к ошибке во время выполнения. Для всех переменных, объявленных как NOT NULL, должно быть указано NOT NULL значение по умолчанию

{DEFAULT | := | = } <выражение>

Указывает начальное значение, присвоенное переменной при входе в блок. Если выражение DEFAULT не указано, переменная инициализируется null-значением SQL

Вместо Oracle PL/SQL-совместимого присваивания := можно использовать =.

Примеры:

account_id CONSTANT integer := 10;
quantity integer DEFAULT 32;
article varchar = 'This is text';

Объявление параметров функции

Параметры, передаваемые функциям, именуются идентификаторами $1, $2 и т.д. При желании для имен параметров можно объявить псевдонимы для повышения читабельности кода. Получить значение параметра можно используя псевдоним или числовой идентификатор. Следующие две функции идентичны:

CREATE FUNCTION deposit_interest(real) RETURNS real AS $$
BEGIN
RETURN $1 * 1.2;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION deposit_interest(total real) RETURNS real AS $$
BEGIN
RETURN total * 1.2;
END;
$$ LANGUAGE plpgsql;

Функции PL/pgSQL также поддерживают выходные (output) параметры. Выходным параметрам присваиваются числовые идентификаторы $1…​$n и им можно задать псевдонимы так же, как и входным параметрам. Выходной параметр — переменная, которую необходимо определить во время выполнения функции. Возвращается последнее присвоенное значение. Выходные параметры наиболее полезны при возврате нескольких значений:

CREATE FUNCTION calculations (x int, y int, OUT sum int, OUT prod int) AS $$
BEGIN
    sum := x + y;
    prod := x * y;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM calculations(10, 5);

Результат:

 sum | prod
-----+------
  15 |   50

Псевдонимы

Можно использовать выражение ALIAS FOR, чтобы объявить псевдоним для переменной или параметра функции.

<новое_имя> ALIAS FOR <старое_имя>;

В следующем примере создается псевдоним variable2 для переменной variable1:

DECLARE
  variable2 ALIAS FOR variable1;

В приведенном ниже примере объявляется псевдоним parameter2 для второго параметра функции:

DECLARE
    parameter2 ALIAS FOR $2;

Копирование типов

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

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

variable1 pgbench_accounts.abalance%TYPE;

variable3 variable2%TYPE;

Типы строки

Переменная составного типа называется переменной строки или переменной типа строки (row-type). Такая переменная может содержать целую строку результата запроса SELECT или FOR. Вы можете объявить переменную строки того же типа, что и строки существующей таблицы или представления. Используйте для этого нотацию <имя_таблицы>%ROWTYPE:

row_var pgbench_accounts%ROWTYPE;

Доступ к отдельным полям значения строки осуществляется с использованием точки, например my_row.field. Параметр функции может иметь составной тип (строка таблицы). В этом случае соответствующий идентификатор $n будет переменной-строкой, а поля из него выбираются следующим образом: $1.field1. В приведенном ниже примере используются таблицы из статьи Пример работы с таблицами через psql.

CREATE FUNCTION get_author(t_row book) RETURNS text AS $$
DECLARE
    author text;
BEGIN
    SELECT name INTO author FROM author WHERE id = t_row.author_id;
    RETURN author;
END;
$$ LANGUAGE plpgsql;

SELECT get_author(b.*), title FROM book b;

Результат:

     get_author      |         title
---------------------+-----------------------
 Virginia Woolf      | Mrs. Dalloway
 Virginia Woolf      | To the Lighthouse
 Harper Lee          | To Kill a Mockingbird
 F. Scott Fitzgerald | The Great Gatsby
 J.R.R. Tolkien      | The Lord of the Rings
 George Orwell       | 1984
 George Orwell       | Animal Farm

Тип record

Переменные record аналогичны переменным типа строки, но у них нет предопределенной структуры. Они принимают структуру строки, назначенной им во время команды SELECT или FOR. Структура переменной типа record может быть изменена каждый раз при её назначении. Пока переменная типа record не будет установлена впервые, она не имеет структуры и любая попытка доступа к её полю приведет к ошибке во время выполнения.

Пример:

CREATE FUNCTION get_book_name(int) RETURNS text AS $$
DECLARE
    my_row record;
BEGIN
    SELECT * INTO my_row FROM book WHERE id = $1;
    RETURN my_row.title;
END;
$$ LANGUAGE plpgsql;

SELECT get_book(5);

Результат:

     get_book_name
-----------------------
 The Lord of the Rings

Выполнение SQL-команд

Любую команду SQL, которая не возвращает строки, можно выполнить внутри функции PL/pgSQL, просто написав команду. Например, можно создать и заполнить таблицу:

CREATE TABLE shelves (
    book_id SERIAL PRIMARY KEY,
    location VARCHAR(10) NOT NULL);

INSERT INTO shelves VALUES (5, 'B32');

Когда команда возвращает строки (например, SELECT или INSERT/UPDATE/DELETE с RETURNING), есть две возможности. Если команда возвращает не более одной строки или вас интересует только первая строка вывода, используйте выражение INTO для сохранения вывода. См. Executing a command with a single-row result. INTO используется в примере выше:

...
 SELECT * INTO my_row FROM book WHERE id = $1;
...

Чтобы обработать все выходные строки, напишите команду в качестве источника данных для цикла FOR, как описано в Looping through query results. Следующая функция обновляет все материализованные представления, используя этот подход:

CREATE FUNCTION refresh_mat_views() RETURNS integer AS $$
DECLARE
    mviews RECORD;
BEGIN
    RAISE NOTICE 'Refreshing all materialized views...';

    FOR mviews IN
       SELECT n.nspname AS mv_schema,
              c.relname AS mv_name,
              pg_catalog.pg_get_userbyid(c.relowner) AS owner
         FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace)
        WHERE c.relkind = 'm'
     ORDER BY 1
    LOOP

        -- В "mviews" есть одна запись для каждого материализованного представления

        RAISE NOTICE 'Refreshing materialized view %.% (owner: %)...',
                     quote_ident(mviews.mv_schema),
                     quote_ident(mviews.mv_name),
                     quote_ident(mviews.owner);
        EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name);
    END LOOP;

    RAISE NOTICE 'Done refreshing materialized views.';
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

SELECT refresh_mat_views();

Неоптимизируемые команды SQL (также называемые служебными командами) не принимают параметры запроса. В таких командах не работает автоматическая подстановка переменных PL/pgSQL. Чтобы включить непостоянный текст в служебную команду, выполняемую из PL/pgSQL, создайте команду как строку, а затем используйте EXECUTE для её запуска, как описано в Executing dynamic commands. В приведенном выше примере EXECUTE используется для запуска REFRESH MATERIALIZED VIEW:

...
EXECUTE format('REFRESH MATERIALIZED VIEW %I.%I', mviews.mv_schema, mviews.mv_name);
...

Иногда необходимо вычислить выражение или запрос SELECT, но отбросить результат, например, при вызове функции, у которой есть побочные эффекты, но нет полезного результата. Чтобы сделать это в PL/pgSQL, используйте оператор PERFORM:

PERFORM <query>;

Пример:

 PERFORM pg_advisory_lock(1234);

Управляющие структуры

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

RETURN

RETURN позволяет возвращать данные из функции.

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

...
RETURN total * 1.2;
...
RETURN my_row.title;
...

Когда объявлено, что функция PL/pgSQL возвращает SETOF <sometype>, отдельные возвращаемые элементы определяются последовательностью команд RETURN NEXT или RETURN QUERY, а затем последняя команда RETURN без аргументов используется для обозначения того, что функция завершила выполнение. RETURN NEXT можно использовать как со скалярными, так и с составными типами данных; при составном типе результата будет возвращена целая "таблица" результатов. RETURN QUERY добавляет результаты выполнения запроса к набору результатов функции.

Пример RETURN NEXT
CREATE OR REPLACE FUNCTION get_all_books() RETURNS SETOF book AS $$
DECLARE
    row book%ROWTYPE;
BEGIN
    FOR row IN SELECT * FROM book
    LOOP
    -- здесь можно произвести необходимую обработку значений
    -- таблица "book" не изменяется, строка "processed by RETURN NEXT" добавляется только в вывод
    row.title= row.title||' processed by RETURN NEXT';
    RETURN NEXT row;
    END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT title FROM get_all_books();

Результат:

                     title
------------------------------------------------
 The Great Gatsby processed by RETURN NEXT
 The Lord of the Rings processed by RETURN NEXT
 1984 processed by RETURN NEXT
 Animal Farm processed by RETURN NEXT
 Mrs. Dalloway processed by RETURN NEXT
 To the Lighthouse processed by RETURN NEXT
 To Kill a Mockingbird processed by RETURN NEXT
Пример RETURN QUERY
CREATE OR REPLACE FUNCTION get_books_by_author(int) RETURNS SETOF book AS $$

BEGIN
    RETURN QUERY SELECT * FROM book WHERE author_id=$1;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'No books';
    END IF;

    RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_books_by_author(3);

Результат:

 id |      title       | author_id | public_year
----+------------------+-----------+-------------
  4 | The Great Gatsby |         3 |        1925

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

Вызов процедур

Функция, процедура или блок DO в PL/pgSQL могут вызывать процедуры с помощью CALL. Выходные параметры обрабатываются иначе, чем CALL в простом SQL. Каждый параметр процедуры OUT или INOUT должен соответствовать переменной в инструкции CALL, и все, что возвращает процедура, присваивается обратно этой переменной после её возврата. Например:

CREATE PROCEDURE triple(INOUT x int)
LANGUAGE plpgsql
AS $$
BEGIN
x := x * 3;
END;
$$;

DO $$
DECLARE myvar int := 5;
BEGIN
CALL triple(myvar);
RAISE NOTICE 'myvar = %', myvar; -- выведет 15
END;
$$;

Условные операторы

Операторы IF и CASE позволяют выполнять альтернативные варианты команд в зависимости от определенных условий. В PL/pgSQL доступны три формы IF:

  • IF …​ THEN …​ END IF

  • IF …​ THEN …​ ELSE …​ END IF

  • IF …​ THEN …​ ELSIF …​ THEN …​ ELSE …​ END IF

и две формы CASE:

  • CASE …​ WHEN …​ THEN …​ ELSE …​ END CASE

  • CASE WHEN …​ THEN …​ ELSE …​ END CASE

IF

 

Пример использования IF:

IF number = 0 THEN
result := 'zero';
ELSIF number > 0 THEN
result := 'positive';
ELSIF number < 0 THEN
result := 'negative';
ELSE
-- единственная оставшаяся возможность -- значение равно null
result := 'NULL';
END IF;
CASE

 

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

CASE <логическое_выражение>
WHEN <выражение> [, <выражение> [ ... ]] THEN
<операторы>
[ WHEN <выражение> [, <выражение> [ ... ]] THEN
<операторы>
... ]
[ ELSE
<операторы> ]
END CASE;

Пример:

CASE book_id

    WHEN 1,2 THEN
        msg := 'Author is Virginia Woolf';
    WHEN 3 THEN
        msg := 'Author is Harper Lee';
    WHEN 4 THEN
        msg := 'Author is F. Scott Fitzgerald';
    WHEN 5 THEN
        msg := 'Author is J.R.R. Tolkien';
    WHEN 6,7 THEN
        msg := 'Author is George Orwell';
    ELSE
        msg := 'Author is unknown';
END CASE;

CASE с перебором условий обеспечивает условное выполнение, основанное на истинности логических выражений. Логическое выражение каждого WHEN оценивается по очереди, пока не будет найдено истинное выражение. Затем выполняются соответствующие операторы, и управление передается следующему оператору после END CASE. Последующие выражения в оставшихся WHEN не оцениваются. Если истинный результат не найден, выполняются операторы ELSE. Если ELSE не указано, возникает исключение CASE_NOT_FOUND.

CASE
    WHEN <логическое_выражение> THEN
      <операторы>
  [ WHEN <логическое_выражение> THEN
      <операторы>
    ... ]
  [ ELSE
      <операторы>]
END CASE;

Пример:

CASE
    WHEN balance > 1000 THEN
        status = 'Platinum' ;
    WHEN balance > 500 THEN
        status = 'Gold' ;
    ELSE
        status = 'Base';
END CASE;

Циклы

С помощью операторов LOOP, EXIT, CONTINUE, WHILE, FOR и FOREACH можно организовать повторение серии команд в коде PL/pgSQL.

LOOP

 

LOOP определяет безусловный цикл, который повторяется бесконечно, пока не будет завершен оператором EXIT или RETURN. Необязательная метка может использоваться операторами EXIT и CONTINUE во вложенных циклах, чтобы указать, к какому циклу относятся эти операторы.

[ <<<метка>>> ]
LOOP
    <операторы>
END LOOP [ <метка> ];
EXIT
EXIT [ <метка> ] [ WHEN <логическое_выражение> ];

Оператор EXIT осуществляет выход из цикла. Если для EXIT не указана метка, самый внутренний цикл завершается и следующим выполняется оператор, следующий за END LOOP. Если указана метка, она должна быть меткой текущего или внешнего уровня вложенного цикла или блока. Затем именованный цикл или блок завершается, и управление продолжается с помощью оператора после соответствующего END.

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

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

CONTINUE
CONTINUE [ <метка> ] [ WHEN <логическое_выражение> ];

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

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

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

В следующем примере используются LOOP, EXIT и CONTINUE для вычисления суммы четных чисел от 1 до заданного параметра:

CREATE OR REPLACE FUNCTION get_sum(int) RETURNS int AS $$
DECLARE
   num int := 0;
   my_sum int :=0;
BEGIN

  LOOP
     num = num + 1;

	 -- Выйти из цикла, если num > переданного параметра
	 EXIT WHEN num > $1;

	 -- Пропустить итерацию, если num является нечетным числом
	 CONTINUE WHEN mod(num,2) != 0;
	 my_sum = my_sum + num;
  END LOOP;
  RETURN my_sum;
END;
$$ LANGUAGE plpgsql;

SELECT get_sum(12);

Результат:

 get_sum
---------
      42
WHILE

 

Оператор цикла WHILE имеет следующий синтаксис:

[ <<<метка>>> ]
WHILE <логическое_выражение> LOOP
<операторы>
END LOOP [ <метка> ];

Оператор WHILE повторяет последовательность операторов до тех пор, пока логическое выражение истинно. Выражение проверяется непосредственно перед каждым входом в тело цикла.

В этом коде с помощью WHILE переписана функция get_sum из примера выше, которая вычисляет сумму четных чисел:

CREATE OR REPLACE FUNCTION get_sum(int) RETURNS int AS $$
DECLARE
   my_sum int :=0;
   num int :=0;
BEGIN

  WHILE num <= $1 LOOP
  	num = num +1;
    IF mod(num,2) = 0 THEN
        my_sum = my_sum + num;
	END IF;
  END LOOP;
  RETURN my_sum;
END;
$$ LANGUAGE plpgsql;

SELECT get_sum(12);

Результат:

 get_sum
---------
      42
FOR

 

Синтаксис оператора цикла FOR, который перебирает диапазон целых чисел:

[ <<метка>> ]
FOR <счетчик> IN [ REVERSE ] <выражение> .. <выражение> [ BY <выражение> ] LOOP
<операторы>
END LOOP [ метка ];

Переменная counter определена как целое число и существует только внутри цикла (любое существовавшее ранее определение переменной counter внутри цикла игнорируется). Два выражения, задающие нижнюю и верхнюю границу диапазона, вычисляются один раз при входе в цикл. Если предложение BY не указано, шагом итерации является 1, в противном случае это значение, указанное в предложении BY. Если указано REVERSE, значение шага вычитается, а не добавляется после каждой итерации.

В этом коде с помощью FOR переписана функция get_sum из примера выше, которая вычисляет сумму четных чисел:

CREATE OR REPLACE FUNCTION get_sum(int) RETURNS int AS $$
DECLARE
   my_sum int :=0;
  BEGIN

  FOR i in 1..$1 LOOP
    IF mod(i,2) = 0 THEN
        my_sum = my_sum + i;
	END IF;
  END LOOP;
  RETURN my_sum;
END;
$$ LANGUAGE plpgsql;

SELECT get_sum(12);

Результат:

 get_sum
---------
      42

Кроме того, в PL/pgSQL есть цикл FOREACH для перебора элементов массива. См. Looping through arrays.

Пример

В этом примере создаются таблицы со случайными данными.

  1. Создайте функцию, которая возвращает строку указанной длины из заданных символов:

    CREATE OR REPLACE FUNCTION generate_text(len int) RETURNS text AS $$
DECLARE
    symbols text := 'aAbBcCdDeEfFgGhHiIjJkKlLmMnNoOpPqQrRsStTuUvVwWxXyYzZ';
    result text := '';
    place int := 0;
    symbols_lenght int = length(symbols);
BEGIN
    WHILE LENGTH(result) < len
    LOOP
    place = floor(random()*symbols_lenght);
        result := result || SUBSTRING(symbols, place, 1);
    END LOOP;

    RETURN result;
END
$$ LANGUAGE 'plpgsql';

  2. Напишите процедуру, которая создает указанное количество таблиц, используя функцию generate_text для их заполнения. Необходимое количество таблиц передается в качестве параметра:

    CREATE OR REPLACE PROCEDURE create_tables(num int, t_name text, row_number int)AS $$
    DECLARE
    table_name text;
    BEGIN
    FOR i IN 1 .. num
        LOOP
            table_name= t_name || '_' || i;
            EXECUTE ('CREATE TABLE ' || table_name || '(id int, text varchar);');
            FOR j IN 1 .. row_number
                LOOP
                    -- Номер строки определяет количество символов в столбце "текст"
                    EXECUTE ('INSERT INTO ' || table_name || ' VALUES (' || j ||','||''''|| generate_text(j)||'''' || ');');
                END LOOP;
        END LOOP;
    END
$$ LANGUAGE 'plpgsql';

  3. Вызовите процедуру create_tables. Первый параметр (3) указывает количество создаваемых таблиц, второй параметр (my_table) задает префикс для имен таблиц, а третий (25) — количество строк в таблицах.

    CALL create_tables(3, 'my_table', 25);

    В результате будут созданы таблицы my_table_1, my_table_2 и my_table_3.

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней