Postgresql как написать скрипт

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

      Сегодня впервые мне пришлось столкнуться с написанием скриптов на 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. Функции;

Содержание

	Введение
	PostgreSQL скрипт из Bash
	Запуск скрипта из файла
	Импорт из .csv файла
	Похожие статьи

Введение

PostgreSQL скрипт из Bash

Чтобы выполнить

Bash

скрипт

с командой PostgreSQL достаточно создать файл

script.sh

подобного содержания

#!/bin/bash
PGPASSWORD=ПАРОЛЬ psql -h ХОСТ -U ИМЯ_ПОЛЬЗОВАТЕЛЯ -d ИМЯ_БД -с "ВАША КОМАНДА"

Например

#!/bin/bash
PGPASSWORD=QWERTY psql -h 127.0.0.1 -U andrei -d urnsu -с "SELECT * FROM news"

И вызвать его

. script.sh

#!/bin/bash
cat sql_script.sql | PGPASSWORD=QWERTY psql -h 127.0.0.1 -U andrei -d urnsu

. bash_script.sh

Запуск скрипта из файла

В Windows

CREATE TABLE person (
id int,
first_name VARCHAR(50),
last_name VARCHAR(50),
gender VARCHAR(5),
date_of_birth DATE
)

cat script.sql | psql.exe -h localhost -p5433 -U postgres HeiHei_ru_DB

Password for user postgres:

CREATE TABLE

CREATE TABLE booking_sites (
id BIGSERIAL NOT NULL PRIMARY KEY,
company_name VARCHAR(50) NOT NULL,
origin_country VARCHAR(50) NOT NULL,
age VARCHAR(3) NOT NULL,
date_of_birth DATE NOT NULL,
website_url VARCHAR(50)
);

Теперь запустим этот скрпит уже не в тестовую а в рабочую базу данных
heihei (которая совпадает с названием сайта HeiHei.ru,
но если написать .ru будет синтаксическая ошибка ERROR: syntax error at or near «.»)

cat booking_sites.sql | psql.exe -h localhost -p5433 -U postgres heihei

Password for user postgres:

CREATE TABLE

В Linux

Выполним следующий скрипт из файла

my_script.sql

SELECT data FROM certificates WHERE (id=’8c585c96-a593-456d-4a87-4d12322c6063′);

cat my_script.sql | psql -h localhost -p5432 -d db_name -U andrei

Password for user andrei:

data
——————————————————————
——BEGIN CERTIFICATE—— +
MIIDDDCCAfSgAwIBAgIIBkh3ZXTEqB8wDQYJKoZIhvcNAQELBQAwGzEZMBcGA1UE+
AxMQUHJpdlggd2ViIHNlcnZlcjAeFw0yMjA3MTkxMzQ0MDJaFw0yMjA3MjAxMzQ0+
MDJaMBsxGTAXBgNVBAMTEFByaXZYIHdlYiBzZXJ2ZXIwggEiMA0GCSqGSIb3DQEB+
AQUAA4IBDwAwggEKAoIBAQCpOJuP+kmgcNlxnfZ9nBwpvSO8LANeW6DuAgc9D4P0+
RI5qGvF5duVNV7DrY3V3+Lwu4udWUUUnm7NvKbR9vaUcB7tx+2O1SXFwAO0Z0UA5+
nswTlYz0Hblw0Z1SIlhv2VValv6jP66KL37mAYc1QObxNwH4wqEPePix0DUlm0+w+
r8qHKPhpAwL5Haf00zJWwc5t1ENNLhGSG90eYhas+cVkdnWgxn0fbcc23R7QU1Bg+
KxLyT23ptqzlRq/6Vdlt1DDt6/sd92ndY/72txUUA1aT9Ycw+4lK2Z7uIxUXGy65+
iz3gu2THWRll82HBFPrsFKthK+hGvwF/IhBiR9ly8FgJAgMBAAGjVDBSMA4GA1Ud+
DwEB/wQEAwIFoDATBgNVHSUEDDAKBggrBgEFBQcDATAMBgNVHRMBAf8EAjAAMB0G+
A1UcDgQWBBSVXlTlVVB5a4yYYlbJ/SwWAjPo/zANBgkqhkiG9w0BAQsFAAOCAQEA+
PmpmWqcBZo4b8JuBrhcI1hGR5lNkq7/eWZYkCvPLjzkDAnWdf4SZZQNPE6O24xAx+
aQNcn2PeBaO5eJVuZwEQMqoRr2j9CO637XRPv0zAOKCuKfcnGuUrerKLiuSQDT1Y+
hwXzp0lCWO8BT/chIJvjSPHYVKgJimXi9oLtf4QvPvliBt0dY/J6LAnewsEYD9ug+
iUA8iCpAITBVDybF462d+m4L5YN0gmLx+sGhPqNHjIWP8HN73HwvIvuCL+lTAk2M+
FGmYVaqthWDts4UNwdaVA1ZnmJVOjklhn2Jm6yKRNs7ZJ2kESX0k+gj33x0AAdcz+
Q5hgzQSUF3wc4rTnFW90/g== +
——END CERTIFICATE—— +

(1 row)

Импорт из .csv файла

Чтобы файл открылся желательно положить его в папку

• C:UsersPublic — для Windows
• /tmp — для
  Linux
  и
  Unix

Либо грамотно настроить разрешения — иначе будет

ошибка

Пример скрипта для импорта

set datestyle to "US";
COPY public.people (id, date, name, address, comment)
FROM '/tmp/file.csv'
DELIMITER ','
CSV HEADER ENCODING 'UTF8'
QUOTE '"'
ESCAPE '''';

Похожие статьи

	PostgreSQL
	Основы
	Установка
	postgresql.conf: Конфигурационный файл
	Таблицы
	Пользователи
	SELECT
	WHERE
	bash скрипты для Postgres
	Репликация master slave
	Write Ahead Log
	recovery.conf
	pg_hba.conf
	Help
	Ошибки

Здравствуйте.

Недавно заинтересовался написанием скриптов на pgScript.

Все что удалось найти из мануалов — вот это http://www.pgadmin.org/docs/dev/pgscript.html#function2

Мануал доступный, но, как мне кажется, тонковат. Надо бы больше информации с примерами. А где ее взять?
Порыл в сети, ничего толкового не нашел. Тем более на русском языке. В связи с этим начал сам экспериментировать и потихоньку писать некое подобие мануала (pgScript. Написание простых скриптов. Первый опыт. ). Это только начало. Хочу написать еще глубже и подробнее. Может кто-нить может указать место, или скинуть если есть инфу по pgScript. Чем больше тем лучше.
Заранее спасибо.  

С Уважением, drmiller.

In this article, you will learn how to execute sql or script file in PostgreSQL on Windows and Linux step by step.

Create and Execute script in POstgreSQL on Linux Environment:

On Linux, We can execute scripts files in two ways:

1. From Linux shell
2. From psql shell

Execute PostgreSQL sql script from Linux shell:

1. Create a script file.

 cat > createdatabase.sql
create database test;
c test
create table test123(name varchar(40), age int);

2. Switch to postgres user.

sudo su - postgres

3. Then, execute the sql script using following syntax:

psql -U postgres -f /home/r2schools/createdatabase.sql

Execute PostgreSQL sql script from psql shell:

1. Create a script file.

cat > createtable.sql
c test
CREATE TABLE students(SNO int PRIMARY KEY, SNAME varchar(50),DOB date, class int, gender varchar(5));

2. Connect to postgresql using psql shell and run the following command.

i  /home/r2schools/createtable.sql

3. Lets verify the table is created or not.

test=# dt
          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | students | table | postgres
 public | test123  | table | postgres

Create and Execute script in PostgreSQL on Windows Environment:

Execute PostgreSQL sql script from psql shell On Windows:

1. Create a script file and paste the following commands and save file.

create database test;
c test
create table test123(name varchar(40), age int);

2. Open psql from start menu

Here, path should be in single quotes and forward slashes must be given.

 i 'C:/Test/createdatabase.sql'

Execute PostgreSQL sql script from Windows command prompt:

1. Create a script file.

create database test;
c test
create table test123(name varchar(40), age int);

2. Now open command prompt as administrator and run the following command to execute a script as shown below.

C:WINDOWSsystem32>psql -U postgres -f C:Testcreatedatabase.sql

Password for user postgres:
Output:

CREATE DATABASE
You are now connected to database "test" as user "postgres".
CREATE TABLE

If we want to stop script when an error occurred while executing script, then use ON_ERROR_STOP=on

Examples:

psql -U postgres -f /home/r2schools/createdatabase.sql ON_ERROR_STOP=on