14 Занятие 14: Расширяемость / Создание расширений

student$ mkdir /home/student/tmp/uom

/home/student/tmp/uom/uom.control

default_version = '1.0'
relocatable = true
encoding = UTF8
comment = 'Единицы измерения'

/home/student/tmp/uom/uom--1.0.sql

\echo Use "CREATE EXTENSION uom" to load this file. \quit

-- Справочник единиц измерения
CREATE TABLE uoms (
    uom text PRIMARY KEY,
    k numeric NOT NULL
);
GRANT SELECT ON uoms TO public;
INSERT INTO uoms(uom,k) VALUES ('м',1), ('км',1000), ('см',0.01);

-- Функция для перевода значения из одной единицы
в другую
CREATE FUNCTION convert(value numeric, uom_from text, uom_to text) RETURNS numeric
LANGUAGE sql STABLE STRICT
RETURN convert.value *
    (SELECT k FROM uoms WHERE uom = convert.uom_from) /
    (SELECT k FROM uoms WHERE uom = convert.uom_to);

student$ pg_config --sharedir

/usr/share/postgresql/16

student$ ls `pg_config --sharedir`/extension/pg_background*

/usr/share/postgresql/16/extension/pg_background--1.0--1.4.sql
/usr/share/postgresql/16/extension/pg_background--1.1--1.4.sql
/usr/share/postgresql/16/extension/pg_background--1.2--1.4.sql
/usr/share/postgresql/16/extension/pg_background--1.3--1.4.sql
/usr/share/postgresql/16/extension/pg_background--1.4.sql
/usr/share/postgresql/16/extension/pg_background.control

/home/student/tmp/uom/Makefile

EXTENSION = uom
DATA = uom--1.0.sql

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

student$ sudo make install -C /home/student/tmp/uom

make: Entering directory '/home/student/tmp/uom'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/usr/bin/install -c -m 644 .//uom.control '/usr/share/postgresql/16/extension/'
/usr/bin/install -c -m 644 .//uom--1.0.sql  '/usr/share/postgresql/16/extension/'
make: Leaving directory '/home/student/tmp/uom'

=> CREATE DATABASE ext_extensions;

CREATE DATABASE

=> \c ext_extensions

You are now connected to database "ext_extensions" as user "student".

=> SELECT * FROM pg_available_extensions WHERE name = 'uom';

 name | default_version | installed_version |      comment
------+-----------------+-------------------+-------------------
 uom  | 1.0             |                   | Единицы измерения
(1 row)

=> CREATE EXTENSION uom;

CREATE EXTENSION

=> SELECT * FROM uoms;

 uom |  k
-----+------
 м   |    1
 км  | 1000
 см  | 0.01
(3 rows)

=> SELECT convert(2, 'км', 'м');

        convert
-----------------------
 2000.0000000000000000
(1 row)

=> \dt uoms

        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+---------
 public | uoms | table | student
(1 row)

CREATE EXTENSION uom SCHEMA public;

=> CREATE SCHEMA uom;

CREATE SCHEMA

=> ALTER EXTENSION uom SET SCHEMA uom;

ALTER EXTENSION

=> \dt uom.*

        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+---------
 uom    | uoms | table | student
(1 row)

=> \df uom.*

                                   List of functions
 Schema |  Name   | Result data type |            Argument data types            | Type
--------+---------+------------------+-------------------------------------------+------
 uom    | convert | numeric          | value numeric, uom_from text, uom_to text | func
(1 row)

=> SELECT * FROM uoms;

ERROR:  relation "uoms" does not exist
LINE 1: SELECT * FROM uoms;
                      ^

=> SELECT uom.convert(2, 'км', 'м');

        convert
-----------------------
 2000.0000000000000000
(1 row)

=> SET search_path = uom, public;

SET

=> SELECT * FROM uoms;

 uom |  k
-----+------
 м   |    1
 км  | 1000
 см  | 0.01
(3 rows)

/home/student/tmp/uom/uom.control

default_version = '1.1'
relocatable = true
encoding = UTF8
comment = 'Единицы изменения'

/home/student/tmp/uom/uom--1.0--1.1.sql

\echo Use "CREATE EXTENSION uom" to load this file. \quit

-- Все, что было, отнесем к мерам длины
ALTER TABLE uoms ADD uom_class text NOT NULL DEFAULT 'длина';

-- Добавим единицы измерения массы
INSERT INTO uoms(uom,k,uom_class) VALUES
    ('г', 1,'масса'), ('кг', 1_000,'масса'),
    ('ц', 100_000,'масса'), ('т', 1_000_000,'масса');

-- Функция для перевода значения из одной единицы
в другую
CREATE OR REPLACE FUNCTION convert(
    value numeric,
    uom_from text,
    uom_to text
)
RETURNS numeric AS $$
DECLARE
    uoms_from uoms;
    uoms_to uoms;
BEGIN
    SELECT * INTO uoms_from FROM uoms WHERE uom = convert.uom_from;
    SELECT * INTO uoms_to FROM uoms WHERE uom = convert.uom_to;
    IF uoms_from.uom_class != uoms_to.uom_class THEN
        RAISE EXCEPTION 'Невозможно преобразовать : % -> %',
            uoms_from.uom_class, uoms_to.uom_class;
    END IF;
    RETURN convert.value * uoms_from.k / uoms_to.k;
END;
$$ LANGUAGE plpgsql STABLE STRICT;

/home/student/tmp/uom/Makefile

EXTENSION = uom
DATA = uom--1.0.sql uom--1.0--1.1.sql

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

student$ sudo make install -C /home/student/tmp/uom

make: Entering directory '/home/student/tmp/uom'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/usr/bin/install -c -m 644 .//uom.control '/usr/share/postgresql/16/extension/'
/usr/bin/install -c -m 644 .//uom--1.0.sql .//uom--1.0--1.1.sql
'/usr/share/postgresql/16/extension/'
make: Leaving directory '/home/student/tmp/uom'

=> SELECT name, version, installed
FROM pg_available_extension_versions
WHERE name = 'uom';

 name | version | installed
------+---------+-----------
 uom  | 1.0     | t
 uom  | 1.1     | f
(2 rows)

=> SELECT * FROM pg_extension_update_paths('uom');

 source | target |   path
--------+--------+----------
 1.0    | 1.1    | 1.0--1.1
 1.1    | 1.0    |
(2 rows)

=> ALTER EXTENSION uom UPDATE;

ALTER EXTENSION

=> SELECT convert(2, 'ц', 'кг');

       convert
----------------------
 200.0000000000000000
(1 row)

=> SELECT convert(1, 'м', 'кг');

ERROR:  Невозможно преобразовать : длина -> масса
CONTEXT:  PL/pgSQL function convert(numeric,text,text) line 9 at RAISE

student$ pg_dump ext_extensions | grep -v '^--'

\restrict IwuMWGFngVEyQ3Vg2T3XUBJYTULgdT2IiGhrZrJ0Z3yzQiJIUYHFEZyMmjr2KCJ

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

CREATE SCHEMA uom;

ALTER SCHEMA uom OWNER TO student;

CREATE EXTENSION IF NOT EXISTS uom WITH SCHEMA uom;

COMMENT ON EXTENSION uom IS 'Единицы измерения';

\unrestrict IwuMWGFngVEyQ3Vg2T3XUBJYTULgdT2IiGhrZrJ0Z3yzQiJIUYHFEZyMmjr2KCJ

=> INSERT INTO uoms(uom,k,uom_class) VALUES
    ('верста',1066.8,'длина'), ('сажень',2.1336,'длина');

INSERT 0 2

student$ pg_dump ext_extensions | grep -v '^--'

\restrict LhLP51mM1O6eMjckrlK0yyJeuK30bq8IcAChcqyRhf64KnB7LamK2fa9cs9ow8e

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

CREATE SCHEMA uom;

ALTER SCHEMA uom OWNER TO student;

CREATE EXTENSION IF NOT EXISTS uom WITH SCHEMA uom;

COMMENT ON EXTENSION uom IS 'Единицы измерения';

\unrestrict LhLP51mM1O6eMjckrlK0yyJeuK30bq8IcAChcqyRhf64KnB7LamK2fa9cs9ow8e

=> DELETE FROM uoms WHERE uom IN ('верста', 'сажень');

DELETE 2

/home/student/tmp/uom/uom.control

default_version = '1.2'
relocatable = true
encoding = UTF8
comment = 'Единицы измерения'

/home/student/tmp/uom/uom--1.1--1.2.sql

\echo Use "CREATE EXTENSION uom" to load this file. \quit

-- Добавляем признак предустановленных данных
ALTER TABLE uoms ADD seeded boolean NOT NULL DEFAULT false;
UPDATE uoms SET seeded = true;

SELECT pg_extension_config_dump('uoms', 'WHERE NOT seeded');

/home/student/tmp/uom/Makefile

EXTENSION = uom
DATA = uom--1.0.sql uom--1.0--1.1.sql uom--1.1--1.2.sql

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

student$ sudo make install -C /home/student/tmp/uom

make: Entering directory '/home/student/tmp/uom'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/usr/bin/install -c -m 644 .//uom.control '/usr/share/postgresql/16/extension/'
/usr/bin/install -c -m 644 .//uom--1.0.sql .//uom--1.0--1.1.sql .//uom--1.1--1.2.sql
'/usr/share/postgresql/16/extension/'
make: Leaving directory '/home/student/tmp/uom'

=> ALTER EXTENSION uom UPDATE;

ALTER EXTENSION

=> INSERT INTO uoms(uom, k, uom_class) VALUES
    ('верста', 1066.8, 'длина'), ('сажень', 2.1336, 'длина');

INSERT 0 2

student$ pg_dump ext_extensions | grep -v '^--'

\restrict 7TiHb0FQ9NhgR2gEbIar4aAE5uyKDrcI1j9O9XMYGRnSTHvkrusqjHOZuuZSAv0

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

CREATE SCHEMA uom;

ALTER SCHEMA uom OWNER TO student;

CREATE EXTENSION IF NOT EXISTS uom WITH SCHEMA uom;

COMMENT ON EXTENSION uom IS 'Единицы измерения';

COPY uom.uoms (uom, k, uom_class, seeded) FROM stdin;
верста 1066.8 длина
f
сажень 2.1336 длина
f
\.

\unrestrict 7TiHb0FQ9NhgR2gEbIar4aAE5uyKDrcI1j9O9XMYGRnSTHvkrusqjHOZuuZSAv0

student$ mkdir bookfmt

bookfmt/bookfmt.control

default_version = '0'
relocatable = true
encoding = UTF8
comment = 'Формат издания'

bookfmt/bookfmt--0.sql

\echo Use "CREATE EXTENSION bookfmt" to load this file. \quit

bookfmt/Makefile

EXTENSION = bookfmt
DATA = bookfmt--0.sql

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

student$ sudo make install -C bookfmt

make: Entering directory '/home/student/bookfmt'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/usr/bin/install -c -m 644 .//bookfmt.control '/usr/share/postgresql/16/extension/'
/usr/bin/install -c -m 644 .//bookfmt--0.sql  '/usr/share/postgresql/16/extension/'
make: Leaving directory '/home/student/bookfmt'

=> CREATE EXTENSION bookfmt;

CREATE EXTENSION

bookfmt/bookfmt.control

default_version = '1.0'
relocatable = true
encoding = UTF8
comment = 'Формат издания'

bookfmt/bookfmt--0--1.0.sql

\echo Use "CREATE EXTENSION bookfmt" to load this file. \quit

ALTER EXTENSION bookfmt ADD TYPE book_format;
ALTER EXTENSION bookfmt ADD FUNCTION book_format_to_text;
ALTER EXTENSION bookfmt ADD CAST (book_format AS text);
ALTER EXTENSION bookfmt ADD FUNCTION book_format_area;
ALTER EXTENSION bookfmt ADD FUNCTION book_format_cmp;
ALTER EXTENSION bookfmt ADD FUNCTION book_format_lt;
ALTER EXTENSION bookfmt ADD OPERATOR < (book_format, book_format);
ALTER EXTENSION bookfmt ADD FUNCTION book_format_le;
ALTER EXTENSION bookfmt ADD OPERATOR <= (book_format, book_format);
ALTER EXTENSION bookfmt ADD FUNCTION book_format_eq;
ALTER EXTENSION bookfmt ADD OPERATOR = (book_format, book_format);
ALTER EXTENSION bookfmt ADD FUNCTION book_format_gt;
ALTER EXTENSION bookfmt ADD OPERATOR > (book_format, book_format);
ALTER EXTENSION bookfmt ADD FUNCTION book_format_ge;
ALTER EXTENSION bookfmt ADD OPERATOR >= (book_format, book_format);
ALTER EXTENSION bookfmt ADD OPERATOR CLASS book_format_ops USING btree;

bookfmt/Makefile

EXTENSION = bookfmt
DATA = bookfmt--0.sql bookfmt--0--1.0.sql

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

student$ sudo make install -C bookfmt

make: Entering directory '/home/student/bookfmt'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/usr/bin/install -c -m 644 .//bookfmt.control '/usr/share/postgresql/16/extension/'
/usr/bin/install -c -m 644 .//bookfmt--0.sql .//bookfmt--0--1.0.sql
'/usr/share/postgresql/16/extension/'
make: Leaving directory '/home/student/bookfmt'

=> ALTER EXTENSION bookfmt UPDATE;

ALTER EXTENSION

=> CREATE EXTENSION isn;

CREATE EXTENSION

=> DO $$
DECLARE
    b_id bigint;
    i text;
    i10 isbn;
    i13 isbn13;
BEGIN
    FOR b_id, i IN SELECT book_id, additional->>'ISBN' FROM books LOOP
        BEGIN
            IF length(translate(i,'-','')) = 10 THEN
                i10 := isbn(i);
            ELSE
                i13 := isbn13(i);
            END IF;
        EXCEPTION
            WHEN others THEN
                RAISE NOTICE 'book_id=%: %', b_id, sqlerrm;
        END;
    END LOOP;
END;
$$;

NOTICE:  book_id=20: invalid check digit for ISBN number: "5-7490-0068-9", should be 0
NOTICE:  book_id=61: invalid input syntax for ISBN number: "5-9000242-17-x"
NOTICE:  book_id=19: invalid check digit for ISBN number: "5-7490-0065-1", should be 6
DO

student$ mkdir typo

typo/typo.control

default_version = '1.0'
relocatable = false
encoding = UTF8
comment = 'Подготовка текста по настраиваемым
правилам'

typo/typo--1.0.sql

\echo Use "CREATE EXTENSION typo" to load this file. \quit

-- В таблице предусматриваем столбец seeded, чтобы
отличать предустановленные правила от
пользовательских.

CREATE TABLE typo_rules (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    pattern text NOT NULL,
    replace_by text NOT NULL,
    seeded boolean DEFAULT false
);
GRANT SELECT ON typo_rules TO public;
INSERT INTO typo_rules(pattern, replace_by, seeded) VALUES
    ('(^|\s)\"(\S)',   '\1«\2', true),
    ('(\S)\"(\s|$)',   '\1»\2', true),
    ('(^|\s)-(\s|$)', '\1—\2', true);

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

CREATE FUNCTION typo(INOUT s text) AS $$
DECLARE
    r record;
BEGIN
    FOR r IN (
        SELECT pattern, replace_by
        FROM @extschema@.typo_rules
        ORDER BY id
    )
    LOOP
        s := regexp_replace(s, r.pattern, r.replace_by, 'g');
    END LOOP;
END;
$$ LANGUAGE plpgsql STABLE;

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

SELECT pg_extension_config_dump('typo_rules', 'WHERE NOT seeded');
SELECT pg_extension_config_dump('typo_rules_id_seq', '');

typo/Makefile

EXTENSION = typo
DATA = typo--1.0.sql

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

student$ sudo make install -C typo

make: Entering directory '/home/student/typo'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/bin/mkdir -p '/usr/share/postgresql/16/extension'
/usr/bin/install -c -m 644 .//typo.control '/usr/share/postgresql/16/extension/'
/usr/bin/install -c -m 644 .//typo--1.0.sql  '/usr/share/postgresql/16/extension/'
make: Leaving directory '/home/student/typo'

=> CREATE DATABASE ext_extensions;

CREATE DATABASE

=> \c ext_extensions

You are now connected to database "ext_extensions" as user "student".

=> CREATE SCHEMA typo;

CREATE SCHEMA

=> CREATE EXTENSION typo SCHEMA typo;

CREATE EXTENSION

=> \sf typo.typo

CREATE OR REPLACE FUNCTION typo.typo(INOUT s text)
 RETURNS text
 LANGUAGE plpgsql
 STABLE
AS $function$
DECLARE
    r record;
BEGIN
    FOR r IN (
        SELECT pattern, replace_by
        FROM typo.typo_rules
        ORDER BY id
    )
    LOOP
        s := regexp_replace(s, r.pattern, r.replace_by, 'g');
    END LOOP;
END;
$function$

=> SELECT typo.typo(
    'Вдруг попугай заорал: "Овер-рсан! Овер-рсан!" - и все замерли.'
);

                              typo
----------------------------------------------------------------
 Вдруг попугай заорал: «Овер-рсан! Овер-рсан!» — и
все замерли.
(1 row)

=> INSERT INTO typo.typo_rules(pattern, replace_by)
    VALUES (' +', ' ');

INSERT 0 1

=> SELECT typo.typo(
    '-  Будет, -  сказал Дрозд. - Я уже   букву "к" нарисовал.'
);

                         typo
-------------------------------------------------------
 — Будет, — сказал Дрозд. — Я уже букву «к»
нарисовал.
(1 row)

=> \q

student$ pg_dump --clean --create ext_extensions > ext_extensions.dump

student$ psql -f ext_extensions.dump

SET
SET
SET
SET
SET
 set_config
------------
(1 row)

SET
SET
SET
SET
DROP DATABASE
CREATE DATABASE
ALTER DATABASE
You are now connected to database "ext_extensions" as user "student".
SET
SET
SET
SET
SET
 set_config
------------
(1 row)

SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
CREATE EXTENSION
COMMENT
COPY 1
 setval
--------
      4
(1 row)

student$ psql ext_extensions

=> INSERT INTO typo.typo_rules(pattern, replace_by)
    VALUES ('\.\.\.', '…');

INSERT 0 1

=> SELECT typo.typo(
    'Как это там... Соус пикан. Полстакана уксусу, две луковицы... и перчик.'
);

                                typo
---------------------------------------------------------------------
 Как это там… Соус пикан. Полстакана уксусу, две
луковицы… и перчик.
(1 row)