Полезные скрипты при миграции из Oracle в PostgreSQL

    Во время миграции из Oracle в PostgreSQL с помощью ora2pg встал вопрос с несоответствием типов данных между разными базами. По умолчанию не все колонки конвертируется правильно, а отсутствие в Oracle Boolean и вовсе порождает неоднозначность – часть колонок нужно перенести как числа, часть как логические значения. В тоже время hibernate знает все о типах данных и может создать эталонную схему.

    Итоговый процесс переноса выглядел следующим образом: создание структуры таблиц через ora2pg, исправление структуры по эталонной схеме, перенос данных, конвертация blob и Boolean, добавление отсутствующих в PostgreSQL функций (nvl, nvl2, regexp_substr), создания оставшейся структуры — индексов, view и прочего.

    Под катом накопившиеся за время конвертации sql скрипты для полуавтоматической миграции.

    Подготовка


    В качестве утилиты для конвертации данных использовалась ora2pg. Процесс использования очень хорошо описан в статье.

    Создаем проект в ora2pg, настраиваем проект, и генерируем схему.

    $./export_schema.sh

    Создаем в PostgreSQL схему 'ora_schema' и таблицы по файлу './schema/tables/table.sql'
    Переключаем Hibernate в режим create и создаем еще одну эталонную схему 'hb_schema'. В случае использования view в проекте, число таблиц в разных схемах не сойдется. Hibernate сгенерирует полноценные таблицы вместо view и это следует учитывать.

    Исправление типов колонок


    Смотрим в каких колонках надо произвести изменения

    select
      hb.table_name as table , hb.column_name as column, hb.data_type as hb_type, ora.data_type as ora_type
    from
      information_schema.columns hb
      join information_schema.columns ora on hb.table_name = ora.table_name and hb.column_name = ora.column_name
    where
      hb.table_schema = 'hb_schema'
      and ora.table_schema= 'ora_schema'
      and ora.data_type !=  hb.data_type
    ORDER BY hb.table_name, hb.column_name;

    Замены для простых случаев

    select 'ALTER TABLE ora_schema.' || ora.table_name || ' ALTER COLUMN ' || ora.column_name || ' TYPE ' || hb.data_type
           || case when hb.data_type = 'numeric' then '('|| hb.numeric_precision ||','|| hb.numeric_scale||')' else '' end  || '; '
    from information_schema.columns as hb
      INNER JOIN information_schema.columns as ora on hb.table_name = ora.table_name and hb.column_name = ora.column_name
    where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema'
          and ora.data_type !=  hb.data_type
          and (hb.data_type = 'integer' or hb.data_type = 'numeric' or hb.data_type = 'bigint' or hb.data_type = 'date')
    ORDER BY hb.table_name, hb.column_name;

    Переносим данные


    Перед переносом данных проверяем, что не совпадения типов остались только в колонках boolean и bytea(oid), а все колонки с численными значениями имеют правильную точность и не являются ‘double precision’ во избежание случайного округления.

    По умолчанию процесс копирования идет в один поток. Меняем в конфигурации на нужное число

    JOBS 4
    ORACLE_COPIES 4
    

    Запускаем процесс копирования данных

    ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf

    Обрабатываем boolean


    Для них требуется удалить если есть значение по умолчанию, поменять тип и вернуть значение по умолчанию

    --ALTER TABLE mytabe ALTER COLUMN mycolumn DROP DEFAULT;
    --ALTER TABLE mytabe ALTER mycolumn TYPE bool USING CASE WHEN mycolumn is null then null when mycolumn=0 THEN FALSE ELSE TRUE END;
    --ALTER TABLE mytabe ALTER COLUMN mycolumn SET DEFAULT FALSE;
    
    select case when ora.column_default is not null then
      'ALTER TABLE ora_schema.'|| ora.table_name || ' ALTER COLUMN ' || ora.column_name || ' DROP DEFAULT; ' else '' end ||
      'ALTER TABLE ora_schema.'|| ora.table_name || ' ALTER ' || ora.column_name ||
           ' TYPE bool USING CASE WHEN ' || ora.column_name ||' is null then null WHEN ' || ora.column_name ||'=0 THEN FALSE ELSE TRUE END;'
          || case when cast(ora.column_default as NUMERIC) = 0 then
              'ALTER TABLE ora_schema.'|| ora.table_name || ' ALTER COLUMN ' || ora.column_name || ' SET DEFAULT FALSE'
             when cast(ora.column_default as NUMERIC) = 1 then
             'ALTER TABLE ora_schema.'|| ora.table_name || ' ALTER COLUMN ' || ora.column_name || ' SET DEFAULT TRUE'
           else '' end
    from information_schema.columns as hb
      INNER JOIN information_schema.columns as ora on hb.table_name = ora.table_name
    where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema'
          and hb.column_name = ora.column_name
          and hb.udt_name != ora.udt_name
          and hb.data_type = 'boolean'
    ORDER BY hb.table_name, hb.column_name;

    Обрабатываем oid(bytea)


    Создаем процедуру для конвертации bytea в oid

    CREATE OR REPLACE FUNCTION ora_schema.bytea_to_oid(p_blob bytea)
      RETURNS oid AS
    $BODY$
    declare
      v_oid oid;
      v_int integer;
    begin
      if octet_length(p_blob)=0 then
        v_oid:=null;
      else
        select lo_create(0) into v_oid;
        select lo_open(v_oid, x'20000'::int | x'40000'::int) into v_int;
        select lowrite (0, p_blob) into v_int;
      end if;
      return v_oid;
    END;$BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;
    ALTER FUNCTION ora_schema.bytea_to_oid(bytea)
    OWNER TO postgres;

    Создаем временную колонку

    select
     'ALTER TABLE ora_schema.' || ora.table_name || ' ADD COLUMN ' || hb.column_name || '_oid oid; '
    from information_schema.columns as hb
       JOIN information_schema.columns as ora on hb.table_name = ora.table_name  and hb.column_name = ora.column_name
    where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema'
          and hb.udt_name != ora.udt_name
          and hb.data_type = 'oid'
    ORDER BY hb.table_name, hb.column_name;

    Переносим данные
    select
    'update ' || ora.table_name || ' set ' || hb.column_name || '_oid = bytea_to_oid(' || hb.column_name || ');'
    from information_schema.columns as hb
       JOIN information_schema.columns as ora on hb.table_name = ora.table_name  and hb.column_name = ora.column_name
    where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema'
          and hb.udt_name != ora.udt_name
          and hb.data_type = 'oid'
    ORDER BY hb.table_name, hb.column_name;

    Удаляем старую колонку

    select 'ALTER TABLE ora_schema.' || ora.table_name || ' DROP COLUMN ' || hb.column_name || '; '
    from information_schema.columns as hb
      INNER JOIN information_schema.columns as ora on hb.table_name = ora.table_name       and hb.column_name = ora.column_name
    where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema'
          and hb.udt_name != ora.udt_name
          and hb.data_type = 'oid'
    ORDER BY hb.table_name, hb.column_name;

    Переименовываем временную колонку

    select 'ALTER TABLE ora_schema.' || ora.table_name || ' RENAME COLUMN ' || hb.column_name ||'_oid to '|| hb.column_name||'; '
    from information_schema.columns as hb
      INNER JOIN information_schema.columns as ora on hb.table_name = ora.table_name and hb.column_name ||'_oid' = ora.column_name
    where hb.table_schema = 'hb_schema' and ora.table_schema= 'ora_schema'
          and hb.data_type = 'oid'
    ORDER BY hb.table_name, hb.column_name;

    Добавление отсутствующих в PostgreSQL функций


    Чтобы не было необходимости в переписывании кода, просто создадим, отсутствующие в PostgreSQL, но которые есть в Oracle и используются в проекте.

    last_day(date)

    create function last_day(dt date) returns date
    LANGUAGE SQL
    AS $$
    select cast (date_trunc('month',dt)+interval '1 month' - interval '1 day' as date)
    $$;

    nvl(date, date)

    create function nvl(var1 date, var2 date) returns date
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is null then var2 else var1 end;
    end;
    $$;

    nvl(integer, integer)

    create function nvl(var1 integer, var2 integer) returns integer
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is null then var2 else var1 end;
    end;
    $$;

    nvl(numeric, numeric)

    create function nvl(var1 numeric, var2 numeric) returns numeric
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is null then var2 else var1 end;
    end;
    $$;

    nvl(text, text)

    create function nvl(var1 text, var2 text) returns text
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is null then var2 else var1 end;
    end;
    $$;

    nvl(timestamp with time zone, timestamp with time zone)

    create function nvl(var1 timestamp with time zone, var2 timestamp with time zone) returns timestamp with time zone
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is null then var2 else var1 end;
    end;
    $$;

    nvl(timestamp, timestamp)

    create function nvl(var1 timestamp without time zone, var2 timestamp without time zone) returns timestamp without time zone
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is null then var2 else var1 end;
    end;
    $$;

    nvl(varchar, varchar)

    create function nvl(var1 character varying, var2 character varying) returns character varying
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is null then var2 else var1 end;
    end;
    $$;

    nvl2(date, date, date)

    create function nvl2(var1 date, var2 date, var3 date) returns date
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is not null then var2 else var3 end;
    end;
    $$;

    nvl2(integer, integer, integer)

    create function nvl2(var1 integer, var2 integer, var3 integer) returns integer
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is not null then var2 else var3 end;
    end;
    $$;

    nvl2(numeric, numeric, numeric)

    create function nvl2(var1 numeric, var2 numeric, var3 numeric) returns numeric
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is not null then var2 else var3 end;
    end;
    $$;

    nvl2(text, text, text)

    create function nvl2(var1 text, var2 text, var3 text) returns text
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is not null then var2 else var3 end;
    end;
    $$;

    nvl(timestamp with time zone, timestamp with time zone)

    create function nvl2(var1 timestamp with time zone, var2 timestamp with time zone, var3 timestamp with time zone) returns timestamp with time zone
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is not null then var2 else var3 end;
    end;
    $$;

    nvl2(timestamp, timestamp, timestamp)

    
    create function nvl2(var1 timestamp without time zone, var2 timestamp without time zone, var3 timestamp without time zone) returns timestamp without time zone
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is not null then var2 else var3 end;
    end;
    $$;

    nvl2(varchar, varchar, varchar)

    create function nvl2(var1 character varying, var2 character varying, var3 character varying) returns character varying
    LANGUAGE plpgsql
    AS $$
    begin
    return case when var1 is not null then var2 else var3 end;
    end;
    $$;

    regexp_substr(text, text)

    create function regexp_substr(str text, pattern text) returns text
    LANGUAGE SQL
    AS $$
    SELECT (regexp_matches(str, pattern))[1]
    $$;

    regexp_substr(varchar, varchar)

    create function regexp_substr(str character varying, pattern character varying) returns text
    LANGUAGE SQL
    AS $$
    SELECT (regexp_matches(str, pattern))[1]
    $$;

    trunc(date, varchar)

    create function trunc(dt date, formatstr character varying) returns date
    LANGUAGE plpgsql
    AS $$
    begin
    return date_trunc(formatstr,dt);
    end;
    $$;

    trunc(timestamp with time zone, varchar)

    create function trunc(dt timestamp with time zone, formatstr character varying) returns date
    LANGUAGE plpgsql
    AS $$
    begin
    return date_trunc(formatstr,dt);
    end;
    $$;

    trunc(timestamp, varchar)

    create function trunc(dt timestamp without time zone, formatstr character varying) returns date
    LANGUAGE plpgsql
    AS $$
    begin
    return date_trunc(formatstr,dt);
    end;
    $$;

    Текстовые поля


    В Oracle нет неограниченного текстового поля, вместо этого используется Lob. В PostgreSQL есть специальный тип — text. Чтобы можно было использовать обе базы аннотации текстовых полей должны быть следующими

    @Column(name = "script", nullable = true)
    @Type(type = "org.hibernate.type.MaterializedClobType")
    public String scriptText;


    Для Postgres также используем самописный диалект, знающий о добавленных функциях
    public class PostgresDialect extends PostgreSQL9Dialect {
        public PostgresDialect() {
            super();
            this.registerFunction("nvl", new StandardSQLFunction("nvl"));
        }
    
        public SqlTypeDescriptor getSqlTypeDescriptorOverride(int sqlCode) {
            Object descriptor;
            switch (sqlCode) {
                case 2005:
                    descriptor = LongVarcharTypeDescriptor.INSTANCE;
                    break;
                default:
                    descriptor = super.getSqlTypeDescriptorOverride(sqlCode);
            }
            return (SqlTypeDescriptor) descriptor;
        }
    }


    Sequence


    Oracle и Postgress имеют разный синтаксис nextval от Sequence.
    Oracle
    SELECT my_seq.nextval;

    Postgres
    select nextval('my_seq');

    Приводим к общему виду. Для этого создаем функции в Postgres и Oracle и везде переписываем на использование этой функции.
    Oracle
    create or replace function seq_nextval(p_sequence_name varchar)
      return integer
    as
      l_statement varchar(4000);
      l_value     integer;
    begin 
      l_statement := 'select '||upper(p_sequence_name)||'.nextval from dual';
      execute immediate l_statement
        into l_value;
      return l_value;
    end;

    Postgres
    create function seq_nextval(p_sequence_name text)
      returns bigint
    as
    $$
       select nextval(p_sequence_name);
    $$
    language sql;

    Использование
    select seq_nextval('my_seq')

    Использовать напрямую функцию Postgres nextval('my_seq') не получиться, так как хотя Oracle и позволит создать данную функцию, но не позволит выбирать значения.

    Transformers.ALIAS_TO_ENTITY_MAP


    Если используется Transformers.ALIAS_TO_ENTITY_MAP то надо обязательно указывать возвращаемые типы. Oracle по умолчанию приведет ключи к верхнему регистру, PotgresSql к нижнему и несовпадение ключей исправляется только вручную.

    Sysdate


    Sysdate нет в PotgresSql, а current_timestamp работает в обоих бд. Заменяем на него

    substring


    В отличие от java, в базах данных индексы начинаются с единицы и правильно указывать substring('str', 1, 2) чтобы получить первые два символа, а не substring('str', 0, 2). Но Oracle допускает и второй вариант. Необходимо исправить и не использовать 0 индекс

    Временные таблицы


    В PostgreSQL в отличии от Oracle, временные таблицы создаются каждый раз и живут в рамках сессии. В Oracle только содержимое временных таблиц живет в каких-либо рамках, а сами таблицы созданы постоянно.
    Из этого проистекают следующие проблемы:
    1) При создании каждой сессии придется создавать временную таблицу. (данный момент можно упростить используя возможность сервера приложений выполнять инициализационный sql-блок при создании новой сессии (connection pool → advanced → Init SQL)
    2) Валидация сущностей в hibernate производится в рамках настроенной схемы (если не указать схему, валидация будет производиться по всем доступным схемам). Т.к. в Postgre временные таблицы создаются в отдельных схемах, валидация провалится.
    (Можно обойти создав реальную таблицу с такой же структурой. При нативных запросах работа будет производиться с временной таблицей, а не с обычной).
    3) Работать с такой временной таблицей возможно только через нативные запросы, т.к. Hibernate во все генерируемые запросы добавляет название схемы (либо надо всю работу со временными таблицами вести через нативные запросы, либо отказываться от временных таблиц и использовать обычную с разграничением данных по уникальному ключу. Оба способа подразумевают переписывание функционала).

    Чтобы продолжить работать с временными таблицами в Postgress, как в Oracle воспользуемся табличными функциями и созданием view на основе табличной функции. Подробности здесь

    Функция создания временных таблиц
    create_permanent_temp_table
    create or replace function create_permanent_temp_table(
     
        p_table_name varchar,
     
        p_schema varchar default null)
     
    returns void as $$
     
    declare
     
        -- https://github.com/yallie/pg_global_temp_tables
     
        v_table_name varchar := p_table_name || '$tmp';
     
        v_trigger_name varchar := p_table_name || '$iud';
     
        v_final_statement text;
     
        v_table_statement text; -- create temporary table...
     
        v_all_column_list text; -- id, name, ...
     
        v_new_column_list text; -- new.id, new.name, ...
     
        v_assignment_list text; -- id = new.id, name = new.name, ...
     
        v_cols_types_list text; -- id bigint, name varchar, ...
     
        v_old_column_list text; -- id = old.id, name = old.name, ...
     
        v_old_pkey_column text; -- id = old.id
     
    begin
     
        -- check if the temporary table exists
     
        if not exists(select 1 from pg_class where relname = p_table_name and relpersistence = 't') then
     
            raise exception 'Temporary table % does not exist. %', p_table_name, 'Create an ordinary temp ' ||
     
                'table first, then use create_permanent_temp_table function to convert it to a permanent one.'
     
                using errcode = 'UTMP1';
     
        end if;
     
     
     
     
        -- make sure that the schema is defined
     
        if p_schema is null or p_schema = '' then
     
            p_schema := current_schema;
     
        end if;
     
     
     
     
        -- generate the temporary table statement
     
        with pkey as
     
        (
     
            select cc.conrelid, format(E',
     
            constraint %I primary key(%s)', cc.conname,
     
                string_agg(a.attname, ', ' order by array_position(cc.conkey, a.attnum))) pkey
     
            from pg_catalog.pg_constraint cc
     
                join pg_catalog.pg_class c on c.oid = cc.conrelid
     
                join pg_catalog.pg_attribute a on a.attrelid = cc.conrelid and a.attnum = any(cc.conkey)
     
            where cc.contype = 'p'
     
            group by cc.conrelid, cc.conname
     
        )
     
        select format(E'\tcreate temporary table if not exists %I\n\t(\n%s%s\n\t)\n\ton commit drop;',
     
            v_table_name,
     
            string_agg(
     
                format(E'\t\t%I %s%s',
     
                    a.attname,
     
                    pg_catalog.format_type(a.atttypid, a.atttypmod),
     
                    case when a.attnotnull then ' not null' else '' end
     
                ), E',\n'
     
                order by a.attnum
     
            ),
     
            (select pkey from pkey where pkey.conrelid = c.oid)) as sql
     
        into v_table_statement
     
        from pg_catalog.pg_class c
     
            join pg_catalog.pg_attribute a on a.attrelid = c.oid and a.attnum > 0
     
            join pg_catalog.pg_type t on a.atttypid = t.oid
     
        where c.relname = p_table_name and c.relpersistence = 't'
     
        group by c.oid, c.relname;
     
     
     
     
        -- generate the lists of columns
     
        select
     
            string_agg(a.attname, ', '),
     
            string_agg(format('new.%I', a.attname), ', '),
     
            string_agg(format('%I = new.%I', a.attname, a.attname), ', '),
     
            string_agg(format('%I %s', a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod)), ', '),
     
            string_agg(format('%I = old.%I', a.attname, a.attname), ' and ')
     
        into
     
            v_all_column_list, v_new_column_list, v_assignment_list, v_cols_types_list, v_old_column_list
     
        from pg_catalog.pg_class c
     
            join pg_catalog.pg_attribute a on a.attrelid = c.oid and a.attnum > 0
     
            join pg_catalog.pg_type t on a.atttypid = t.oid
     
        where c.relname = p_table_name and c.relpersistence = 't';
     
     
     
     
        -- generate the list of primary key columns
     
        select string_agg(format('%I = old.%I', a.attname, a.attname), ' and '
     
            order by array_position(cc.conkey, a.attnum))
     
        into v_old_pkey_column
     
        from pg_catalog.pg_constraint cc
     
            join pg_catalog.pg_class c on c.oid = cc.conrelid
     
            join pg_catalog.pg_attribute a on a.attrelid = cc.conrelid and a.attnum = any(cc.conkey)
     
        where cc.contype = 'p' and c.relname = p_table_name and c.relpersistence = 't'
     
        group by cc.conrelid, cc.conname;
     
     
     
     
        -- if primary key is defined, use the primary key columns
     
        if length(v_old_pkey_column) > 0 then
     
            v_old_column_list := v_old_pkey_column;
     
        end if;
     
     
     
     
        -- generate the view function
     
        v_final_statement := format(E'-- rename the original table to avoid the conflict
     
    alter table %I rename to %I;
     
     
     
    -- the function to select from the temporary table
     
    create or replace function %I.%I() returns table(%s) as $x$
     
    begin
     
        -- generated by pg_global_temp_tables
     
        -- create table statement
     
    %s
     
     
     
        return query select * from %I;
     
    end;
     
    $x$ language plpgsql
     
    set client_min_messages to error;\n',
     
        p_table_name, v_table_name,
     
        p_schema, p_table_name, v_cols_types_list,
     
        v_table_statement, v_table_name);
     
     
     
     
        -- generate the view
     
        v_final_statement := v_final_statement || format(E'
     
    create or replace view %I.%I as
     
        select * from %I.%I();\n',
     
        p_schema, p_table_name, p_schema, p_table_name);
     
     
     
     
        -- generate the trigger function
     
        v_final_statement := v_final_statement || format(E'
     
    create or replace function %I.%I() returns trigger as $x$
     
    begin
     
        -- generated by pg_global_temp_tables
     
        -- create temporary table
     
    %s
     
     
     
        -- handle the trigger operation
     
        if lower(tg_op) = \'insert\' then
     
            insert into %I(%s)
     
            values (%s);
     
            return new;
     
        elsif lower(tg_op) = \'update\' then
     
            update %I
     
            set %s
     
            where %s;
     
            return new;
     
        elsif lower(tg_op) = \'delete\' then
     
            delete from %I
     
            where %s;
     
            return old;
     
        end if;
     
    end;
     
    $x$ language plpgsql set client_min_messages to error;\n',
     
        p_schema, v_trigger_name, v_table_statement, -- function header
     
        v_table_name, v_all_column_list, v_new_column_list, -- insert
     
        v_table_name, v_assignment_list, v_old_column_list, -- update
     
        v_table_name, v_old_column_list); -- delete
     
     
     
     
        -- generate the view trigger
     
        v_final_statement := v_final_statement || format(E'
     
    drop trigger if exists %I on %I.%I;
     
    create trigger %I
     
        instead of insert or update or delete on %I.%I
     
        for each row
     
        execute procedure %I.%I();',
     
        v_trigger_name, p_schema, p_table_name,
     
        v_trigger_name, p_schema, p_table_name,
     
        p_schema, v_trigger_name);
     
     
     
     
        -- create all objects at once
     
        execute v_final_statement;
     
    end;
     
    $$ language plpgsql set client_min_messages to error;


    Функция удаления временных таблиц
    drop_permanent_temp_table
    create or replace function drop_permanent_temp_table(
     
        p_table_name varchar,
     
        p_schema varchar default null)
     
    returns void as $$
     
    declare
     
        -- https://github.com/yallie/pg_global_temp_tables
     
        v_table_name varchar := p_table_name || '$tmp';
     
        v_trigger_name varchar := p_table_name || '$iud';
     
        v_count int;
     
        v_drop_statements text;
     
    begin
     
        -- make sure that the schema is defined
     
        if p_schema is null or p_schema = '' then
     
            p_schema := current_schema;
     
        end if;
     
     
     
     
        -- check if the supporting functions exist
     
        select count(*)
     
        into v_count
     
        from pg_catalog.pg_proc p
     
        join pg_catalog.pg_namespace n on n.oid = p.pronamespace
     
        where p.proname in (p_table_name, v_trigger_name) and
     
            p.pronargs = 0 and n.nspname = p_schema and
     
            p.prosrc like '%pg_global_temp_tables%';
     
     
     
     
        if v_count <> 2 then
     
            raise exception 'The table %.% does not seem to be persistent temporary table. %', p_schema,
     
                p_table_name, 'The function only supports tables created by pg_global_temp_tables library.'
     
                using errcode = 'UTMP2';
     
        end if;
     
     
     
     
        -- generate the drop function statements
     
        v_drop_statements := format(E'-- drop the functions and cascade the view
     
            drop function %I.%I() cascade;
     
            drop function %I.%I() cascade;',
     
            p_schema, p_table_name, p_schema, v_trigger_name);
     
     
     
     
        -- drop the functions
     
        execute v_drop_statements;
     
    end;
     
    $$ language plpgsql set client_min_messages to error;


    Использование
    Создаем обычную временную таблицу
    CREATE temp TABLE filter_table
    (
        id BIGINT NOT NULL,
        id_str VARCHAR(255),
        key VARCHAR(255) NOT NULL,
        fd DATE,
        id_long BIGINT,
        sd DATE,
        CONSTRAINT filter_table_pkey PRIMARY KEY (key, id)
    ) ON COMMIT PRESERVE ROWS;

    Прогоняем ее через функцию генерации:
    select create_permanent_temp_table('filter_table', 'schema_name');
    Поделиться публикацией
    Похожие публикации
    Ой, у вас баннер убежал!

    Ну. И что?
    Реклама
    Комментарии 10
    • 0
      Ещё одна причина пользоваться хэлперами для бд, как при создании, так и при запросах к таблице.
      • 0
        Можете сказать, что вы подразумеваете под хэлперами и чем они помогут при переносе?
        • 0
          Практически для каждого языка программирования существуют хэлперы для работы с БД. Они позволяют вместо написания sql запросов писать на ЯП, при чем, в настройках хэлпера можно указать тип бд, а он в свою очередь уже будет генерировать запрос в зависимости от синтаксиса. Пример
          SELECT * FROM TABLE WHERE ATTR1>1 LIMIT 0, 10
          SELECT TOP 10 * FROM TABLE WHERE ATTR1>1 
          SELECT * FROM TABLE WHERE ARRT1>1 and ROWNUM<=10
          

          В случае хелпера это может выглядить следующим образом
          var sql = helper("mssql").select("*").from("table").where("attr1>1").limit("10")
          

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

          • 0
            По текущему опыту переноса относительно крупного проекта могу сказать, что переписывать кучу запросов не пришлось, а в тех случаях где пришлось хэлпер бы не помог. Встретил лишь два типа проблем или отсутствующие функции (nvl, to_number) или работа с boolean.
            Есть еще некоторое число больших сложных отчетов, где могут возникнуть проблемы, но их писать в хэлпере не удобно. Sql на 200-500 в подобном виде практически не читаемы и их труднее отлаживать.
            • 0
              sql на 200-500 в принципе не очень читабельны ) Но что поделать. Я после приведенного выше примера с переписыванием кучи sql придерживаюсь мнения что лучше писать запросы через хэлперы.
              • 0
                Все зависит от того как писать, если использовать конструкцию with и бить на куски, то 500 строчек sql разбиваются на десяток относительно простых, понятных и легких запросов. Которые можно отлаживать последовательно друг за другом. Не самое приятное дело, но и ничего титанического в этом нет.
                Касательно лимита по числу строк, у нас в проекте используется hibernate + нативные запросы через него и именно этот тонкий вопрос различий БД удалось обойти, но можно ли считать его хэлпером )

      • 0
        Спасибо, мне понравилось.

        Ещё, если интересно, можно вот здесь посмотреть (вдруг, что полезное найдётся):

        www.sqlines.com/home

        www.sqlines.com/oracle-to-postgresql
        • +1
          Похоже, что переход с Oracle на Postgres становится массовым. Еще заметил, что не менее массово проекты переносят с Hadoop/Hive/Spark на тот же Postgres.
          • 0
            Вы цены на Оракл Ентерпайз на число ядер современных серверов умножали? :)
            • 0
              Цены на Оракл стали уж совсем неприличными и дешевле залить просевшую производительность железом. Дополнительно в связи с «импортозамещением» бюджетные организации не очень любят оплачивать стоимость этих лицензий и проще уговорить на более мощное железо.

          Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

          Самое читаемое