Страницы

22 марта 2012 г.

pgScript. Написание простых скриптов. Первый опыт.


Приветствую Вас в блоге "Будни программиста".


      Сегодня впервые мне пришлось столкнуться с написанием скриптов на pgScript. Синтаксис написания скриптов на pgScript сильно отличается от синтаксиса написания тел хранимых процедур на plpgsql, что, честно говоря, в начале написания приводило к жуткой синтаксической путанице. :)


Итак, переходим непосредственно к изучению...



       Типы данных в pgScript.

STRING - Символьный тип данных;
INTEGER  - Целочисленный тип;
REAL - Число с плавающей точной;
RECORD - Тип запись - массив данных.


       Объявление переменных.

      Первое, что бросается в глаза глядя на pgScript - это объявление переменных. В объявлении не указывается тип переменной, а значит нет операций явного преобразования, например:

declare @tst;

или, если необходимо объявить несколько переменных то:

declare @tst, @tst1, @tst2;

Для присваивания значений используется служебное слово SET:
set @A = 1000, @B = 2000;   -- @A и @B числа целого типа;
set @C = 10E1, @D = 1.5;    -- @C и @D числа с плавающей точкой;
set @E = 'ab', @F = 'a''b'; -- @E и @F строковые типы;
set @G = "ab", @H = "a\"b"; -- @G и @H строковые типы;

       Причем имена переменных в скрипте являются "псевдонимами"
значений. Вот пример наглядно показывающий то, что я хочу сказать:

SET @A = EXEC concat_text('abc', 'def'); --выполнение функции
ASSERT @A[0][0] = 'abcdef'; -- если @A[0][0] = 'abcdef'; - лож - генерится исключение.
SET @A = 'abc', @B = 'def';
SET @C = SELECT concat_text('@A', '@B') AS output; -- Опять вызов функции. Обратите внимание на способ указания значений переменных (!). 
      Вывод результатов.

       Вывод данных в течение выполнения скрипта выполняется командой  PRINT:

print 'true';

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

print 'Result '+ cast(@tst as string);

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


      Операторские скобки.

       Операторскими скобками в скриптах является BEGIN...END, а разделителем операторов символ ";":

declare @test;
begin
--Тело скрипта pgScript;
end

       Заметьте, что  после операторских скобок символ ";" не ставится. Он разделяет операторы находящиеся между begin и end.

      Ветвления.

       Для построения нелинейной логики выполнения скрипта можно использовать инструкцию IF. Синтаксис так же отличен от plpgsql. В pgScript нет указания окончания оператора END IF, а так же отсутствует THEN;

if @tst<20 
    print 'true';

       Простой пример скрипта использующий все операторы описанные выше выглядит следующим образом:

declare @tst;
begin
  set @tst = 10;
  if @tst<20 
    print 'true';
end

       Использование SQL-операторов в скрипте.

Используем SELECT:

declare @current_number; --объявление переменной
begin
    --присваиваем значение из запроса
    set @current_number = (select count(*) from documents_assignment);
    --сравниваем результат с нулем
    if cast(@current_number as integer) > 0 
        --если результат больше нуля - используем функцию max для определения истинного максимального числа
        set @current_number = (select max(internal_number) from documents_assignment);
    --инкремент...
    set @current_number = cast(@current_number as integer)+1;
    --вывод результата 
    print 'Result: '+ cast(@current_number as string);
end

       В скрипте описан алгоритм получения числа которое на единицу больше максимального в поле "internal_number". Это был показательный пример, теперь его можно немного оптимизировать:

declare @current_number;
begin
    set @current_number = (select coalesce(max(internal_number),0) from documents_assignment);
    set @current_number = cast(@current_number as integer)+1;
    print 'Result: '+ cast(@current_number as string);
end

Предвидя вопрос: siquence в моем случае использовать невозможно по ряду специфичных причин.

Маленькое отступление.

Не работает конструкция:
set @client_id = (select id_owner from account_owner_account_link
                    where id_account = @orders_record[@orders_rec_number][3] and
                    id_account_owner_type = 2 /*client*/);
Надо будет позже разобраться почему...
Вероятно это связано с тем, что скрипт при парсинге запроса не может распознать необычную для SQL форму записи "where id_account = @orders_record[@orders_rec_number][3]", и выдает синтаксическую ошибку.

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

--объявляем переменные которые будут необходимы
declare @orders_rec_number,
    @orders_max_rec_number, 
    @client_id,
    @account_id,
    @orders_id,
    @orders_date;
    
declare @orders_record; --records...
--начало тела скрипта 
begin
    print 'Begin script';

    --init (блок инициализации переменных)
    set @orders_rec_number = 0;
    set @orders_max_rec_number = 0; 
    set @client_id = 0;
    set @account_id = 0;
    set @orders_id = 0;
    set @orders_date = cast('1900-01-01' as string);
    --end init
    
    --заполнение массива RECORD данными из запроса
    set @orders_record = select id_orders, orders_number, orders_date,  id_account from orders order by id_orders;
    set @orders_max_rec_number = (select count(id_orders) from orders);
    set @orders_max_rec_number = cast(@orders_max_rec_number as integer);
    
    --Цикл. Почему именно так (while 1)? Дело привычки.
    while 1 
    begin
        if (@orders_rec_number = @orders_max_rec_number)
          break;
        --пример получения значения одного элемента из RECORD - массива
        set @account_id = @orders_record[@orders_rec_number][3];
        --пример получения значения из запроса
        set @client_id = (select coalesce(id_owner,0) 
          from account_owner_account_link
        where id_account = @account_id and
          id_account_owner_type = 2 /*client*/);
        --пример сравнивания значение переменной которая может cодержать "NULL"
        if (select coalesce(@client_id,0)<>0)
        begin
            set @client_id = cast(@client_id as integer);
            set @orders_id = @orders_record[@orders_rec_number][0];
            set @orders_date = @orders_record[@orders_rec_number][2];
         
            --пример выполнения хранимой процедуры
            SELECT f_documents_assignment_add(
            '@orders_date', --тип:timestamp...
            '@orders_date', --тип:timestamp...
            cast(@client_id as integer),
            1,
            cast(@orders_id as integer)
            );
        end

        set @orders_rec_number = @orders_rec_number + 1;

    end -- конец цикла

    print 'End script';
    -- конец тела скрипта
end


Это далеко не все возможности pgScript. Далее мы подробнее рассмотрим:
  1. Тип RECORD;
  2. Генераторы последовательностей;
  3. Генераторы случайных значений;
  4. Работа с транзакциями;
  5. Функции;

4 комментария:

  1. А чем отличается pgScript от использования конструкции типа:

    using language plpgsql $$
    declare
    a record;
    begin
    for a in (select name from table)
    loop
    raise info '%', 'name = ' || name;
    end loop
    end;

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

    using language plpython27 $$
    declare
    begin
    end;

    Есть ли какие-то задачи при которых целесообразнее использовать pgScript? Или может для какие-то задачи можно реализовать только с помощью него?

    ОтветитьУдалить
    Ответы
    1. Для реализации рабочих, нагруженных функциональных задач, конструкция типа "using language lang_name $$" безусловно мощнее.
      Но когда возникает необходимость устроить тестирование хранимок, триггеров тут, мне кажется pgScript незаменим. Его простота, возможность чтения из файлов, гибкие "бредо-герераторы" различных типов делают его более юзабельным для написания несложных функциональных и тестировачных скриптов.
      + если стоит pgAdmin - этот скотритовый язык не нужно устанавливать дополнительно.
      + он прост как 2*2.

      Данный пост посвящен именно ему (pgScript), ибо мануалов можно сказать нет, русскоязычные описания вообще трудно найти, почти нереально. Хочется восполнить этот пробел, поэтому и не... "using language..." :)

      Удалить
    2. А вот мануалов действительно мало(. Я потому и пользую "using language...", ну а для тестов unit_test (Пока только разбираюсь)

      Удалить
  2. Одно из важных достоинств PgScript - управление транзакциями. Так как если выполнять созданную plpgsql функцию или блок кода, то мы получаем или всё или ничего. Обычно это полезно, но есть административные задачи, когда именно это есть вред. Я например использую PgScript для работ по страничной обработке больших таблиц. В этом случае мне одна большая транзакция вредна и поэтому вся мощь такого скрипта проявляется в полной мере. Например, когда нужно добавить новое NOT NULL поле. Сначала добавляем его как NULL, а потом начинаем его обновлять постранично, например по 10000 строк. Таким образом у меня есть возможность в любое время прервать/продолжить скрипт без потери ранее обработанных строк + я имею короткие транзакции.

    ОтветитьУдалить