PG1C

-- Получаем список доступных таблиц 1С
select * from pg1c.metadata_tables();

-- Создаем таблицу
select pg1c.create_table('Справочник.Контрагенты');

-- Создаем таблицу с другими схемой и названием (public.Goods) в PostgreSQL
select pg1c.create_table('Справочник.Товары', schema=>'public', table_name_pg=>'Goods');

-- Создаем все таблицы
call pg1c.create_table_all();

-- Получаем список всех созданных таблиц
select * from pg1c.table;

-- Выбираем данные из таблицы,
-- обновление данных выполняется отдельно по расписанию 
select * from Справочник.Контрагенты;

-- Выбираем данные из табличной части 2 способами:
-- detail-представление
select * from Справочник.Контрагенты_КонтактнаяИнформация;
-- поле типа массив master-таблицы
select КонтактнаяИнформация from Справочник.Контрагенты;

-- Обновляем и выбираем данные непосредственно в запросе
select * from Справочник.Контрагенты();

-- Обновляем только нужные данные и выбираем их 2 способами:
-- соединение
select * from myorders m
  cross join Справочник.Контрагенты(m.partner_id);
-- вызов функции
select *,(Справочник.Товары(m.ТоварСсылка)).Наименование from myorders m;

-- Создание ключа в момент создания таблицы
create table myorders(
  id serial primary key, 
  partner_id uuid references Справочник.Контрагенты deferrable initially deferred
);

-- Создание ключа для существующей таблицы 2 способами:
-- сокращенное, имя будет сформировано автоматически 
alter table myorders add foreign key (partner_id)
  references Справочник.Контрагенты deferrable initially deferred;
-- полное с указанием имени и поля первичного ключа
alter table myorders add constraint myorders_partner_fk foreign key (partner_id)
  references Справочник.Контрагенты(Ссылка) deferrable initially deferred;

-- Выдача права чтения таблицы пользователю
grant select on Справочник.Контрагенты to user_1;

-- Выдача права обновления таблицы роли
grant execute on function Справочник.Товары(),Справочник.Товары(uuid) to role_analysts;

-- Выдача разработчику права создания внешнего ключа на таблицу
grant references on table Справочник.Товары to developer_1;

-- ! Выдача прав на табличную часть (представление) не рекомендуется,
-- т.к. при обновлении метаданных из-за особенностей PostgreSQL 
-- может возникнуть исключение
-- Рекомедуется создать функцию, возвращающую запрос, и выдать права на нее
grant select on Справочник.Контрагенты_КонтактнаяИнформация to user_1;

-- ! Создание представлений с непосредственным использовнием таблиц не рекомендуется,
-- т.к. при обновлении метаданных из-за особенностей PostgreSQL может возникнуть исключение
-- Рекомендуется создавать функции-оберки и использовать их в представлениях, см. ниже
create or replace view myview1 as 
  select * from Справочник.Товары where not ПометкаУдаления; 
alter table Справочник.Товары ... ; -- ! Возможно исключение при использовании myview1

-- Функция на языке SQL, выдача прав пользователю
create or replace function myquery1()
  returns setof Справочник.Товары language sql as
$$
  select * from Справочник.Товары where not ПометкаУдаления;
$$;
grant execute on function myquery1 to user_1; 

-- Функция на языке plpgsql, которая: обновляет таблицу, 
-- создает на ее основе временную таблицу, возвращает SQL-запрос  
-- Определение возвращаемых полей необходимо для возможности
-- использования в представлениях      
create or replace function myquery2()
  returns table(Ссылка uuid,ПометкаУдаления boolean) language plpgsql as
$$
begin
  perform Справочник.Товары();
  create temp table temp_myquery2 on commit drop as
    select * from Справочник.Товары limit 10;
  return query select Ссылка,ПометкаУдаления from temp_myquery2;
end 
$$;

-- Функция с параметром и динамическим SQL-запросом
create or replace function myquery3(lim int)
  returns table(Ссылка uuid,ПометкаУдаления boolean) language plpgsql as
$$
begin
  return query  
    execute $SQL$
       select Ссылка,ПометкаУдаления from Справочник.Товары limit $1
    $SQL$
    using lim;
end 
$$;

-- Создание обычного и материализованного представлений на основе функции
-- Обновление метаданных не приведет к исключению, т.к. нет прямой связи с таблицей
create or replace view myview2 as select * from myquery2(); 
create materialized view myview3 as select * from myquery3(20);
refresh materialized view myview3;
grant select on myview2,myview3 to user_1;

-- Создаем таблицу
select pg1c.create_table('Справочник.Контрагенты');

-- Создаем таблицу с другими схемой и названием (public.Goods) в PostgreSQL
select pg1c.create_table('Справочник.Товары', schema=>'public', table_name_pg=>'Goods');

-- Создаем таблицу без загрузки данных для сервера УТ
select pg1c.create_table_only('Справочник.Контрагенты', 'УТ');

-- Создаем все таблицы
call pg1c.create_table_all();

-- Создаем все таблицы для сервера УТ без промежуточного фиксирования транзакций
call pg1c.create_table_only('УТ', null);

-- Получаем список всех созданных таблиц
select * from pg1c.table;

-- Обновляем и выбираем все данные
select * from Справочник.Контрагенты();

-- Обновляем и используем данные на процедурном языке PL/pgSQL
do $$ begin
  perform Справочник.Контрагенты();
  -- Далее используем актуальные данные
  -- select * from Справочник.Контрагенты,Справочник.Контрагенты_КонтактнаяИнформация;
end $$;

-- Обновляем и выбираем по указанной Ссылке
select * from Справочник.Контрагенты('bbb41b6a-768d-11f0-ae5b-bcaec5b7b36a');

-- Обновляем и получаем данные контрагента для каждой строки заказа
select *
  from order o
  cross join Справочник.Контрагенты(o.client_id) 

-- Обновляем и используем данные на процедурном языке PL/pgSQL
do $$ declare 
  товар Справочник.Номенклатура;
begin
  товар := Справочник.Номенклатура('df7d0771-7690-11f0-ae5b-bcaec5b7b36a');
  -- Далее используем актуальные данные из переменной товар
end $$;

-- Обнвляем данные таблицы
call pg1c.refresh_data('Справочник.Товары');

-- Обнвляем данные таблицы для сервера УТ
select pg1c.create_table_only('Справочник.Контрагенты', 'УТ');

-- Обнвляем данные таблиц всех серверов
call pg1c.refresh_data_all();

-- Обнвляем данные небольших таблиц для сервера УТ в одной транзакции
call pg1c.refresh_data_all('УТ', fast=>true, commit_tables=>null);

-- Обновляем метаданные (структуру таблицы) и данные при необходимости
call pg1c.refresh_metadata('Справочник.Контрагенты');

-- Обновляем метаданные и данные в любом случае
call pg1c.refresh_metadata('Справочник.Товары', refresh_data=>true);

-- Обновляем только метаданные, в переменную v_refresh_data получаем необходимость
-- обновления данных, после выполнения таблица может быть пустой 
do $$ declare 
  v_refresh_data boolean;
begin
  call pg1c.refresh_metadata_only('Справочник.Товары', refresh_data=>v_refresh_data);
end $$;

-- Обнвляем метаданные и данные таблиц всех серверов
call pg1c.refresh_data_all();

-- Обнвляем метаданные и данные таблиц для сервера УТ в одной транзакции
select pg1c.refresh_data_all('УТ', commit_tables=>null);

-- Удаляем таблицу
call pg1c.drop_table('Справочник.Контрагенты');

-- Удаляем таблицу для сервера УТ
select pg1c.create_table_only('Справочник.Контрагенты', 'УТ');

-- Удаляем все таблицы для сервера по умолчанию (DEFAULT)
call pg1c.drop_table_all();

-- Удаляем все таблицы для сервера УТ без промежуточного фиксирования транзакций
call pg1c.drop_table_all('УТ', null);

-- Устанавливаем расширение и настраиваем параметры подключения сервера DEFAULT
create extension pg1c;
update pg1c.server_1c set web_address='192.168.1.10',publication='Acc',user_1c='odata',password_1c='[пароль]';"

-- Получаем URL для проверки в WEB-браузере
select pg1c.http_url();

-- Получаем список серверов (при выполнении в SQL-менеджере можно редактировать)
select * form pg1c.server_1c;

-- Добавляем новый сервер УТ
insert into pg1c.server_1c(id, web_address, publication, user_1c)
  values ('УТ', '192.168.1.10', 'TM', 'Администратор (ФедоровБМ)');

-- Для сервера УТ добавляем префикс "УТ_" к схеме создаваемых таблиц 
update pg1c.server_1c set schema_expression=$$ 'УТ_'||$1 $$ where id='УТ';

-- Устанавливаем расширение и настраиваем параметры подключения сервера DEFAULT
create extension pg1c;
update pg1c.server_1c set web_address='192.168.1.10',publication='Acc',user_1c='odata',password_1c='[пароль]';"

-- Получаем URL для проверки в WEB-браузере
select pg1c.http_url();

-- Получаем список серверов (при выполнении в SQL-менеджере можно редактировать)
select * form pg1c.server_1c;

-- Добавляем новый сервер УТ
insert into pg1c.server_1c(id, web_address, publication, user_1c)
  values ('УТ', '192.168.1.10', 'TM', 'Администратор (ФедоровБМ)');

-- Для сервера УТ добавляем префикс "УТ_" к схеме создаваемых таблиц 
update pg1c.server_1c set schema_expression=$$ 'УТ_'||$1 $$ where id='УТ';

-- Получаем список всех таблиц 1С
select * from pg1c.table;

-- Получаем список всех таблиц 1С для сервера УТ 
select * from pg1c.table where server_1c='УТ' order by schema,name_pg;

-- Для указанной таблицы устанавливаем кол-во строк, получаемых за один HTTP-запрос
update pg1c.table set fetch_size=5000 where name_1c='Справочник.Контрагенты';

-- Проверяем актуальность данных и метаданных, получая самые ранние моменты обновлния.
-- Рекомендуется использовать для мониторинга процесса обновления по расписанию
select min(refresh_data_timestamp),min(refresh_metadata_timestamp) from pg1c.table;

-- Получаем продолжительность обновления данных всех таблиц
select sum(refresh_data_duration) from pg1c.table;

-- Получаем список доступных таблиц 1С
select * from pg1c.metadata_tables();

-- Получаем список доступных таблиц 1С сервера УТ
select table_1c from pg1c.metadata_tables('УТ') order by 1;

-- Получаем список доступных таблиц 1С, которых нет в PostgreSQL
select * 
  from pg1c.metadata_tables()
  where table_1c not in (select name_1c from pg1c.table);

-- Получаем URL для получения метаданных
select pg1c.http_url();

-- Получаем URL для данных таблицы Справочник.Контрагенты сервера УТ
select pg1c.http_url('Catalog_Контрагенты', 'УТ');

-- Получаем URL для данных конкретной строки (определяется полем Ссылка типа UUID) в формате JSON 
select pg1c.http_url('Catalog_Товары')||'?$format=json&$filter=Ref_Key eq guid''bbb41b67-768d-11f0-ae5b-bcaec5b7b36a''';

-- Получение значений составного поля различными способами
select СоставноеПоле,СоставноеПоле::text,(СоставноеПоле).Value,(СоставноеПоле).Type from Справочник.Товары;

-- Получение только ссылок на таблицу Справочник.Контрагенты
select (СоставноеПоле).value::uuid Ссылка from Справочник.Товары where (СоставноеПоле).Type='Справочник.Контрагенты';

-- Создаем значения преобразованием 
select 
  (123.45,'numeric')::pg1c.type_any,
  ('bbb41b6a-768d-11f0-ae5b-bcaec5b7b36a'::uuid,'Справочник.Контрагенты')::pg1c.type_any;

-- Создаем значения различных типов с помошью функции pg1c.value_any
select
  pg1c.value_any('123.45','Edm.Double'),                                                      -- Число
  pg1c.value_any('bbb41b6a-768d-11f0-ae5b-bcaec5b7b36a','StandardODATA.Catalog_Контрагенты'), -- (Ссылка,Справочник.Контрагенты)
  pg1c.value_any('Зеленый','StandardODATA.Цвет'),                                             -- Перечисление
  pg1c.value_any('','StandardODATA.Undefined')                                                -- Непределенное значение, (null,'null') 

-- Получаем версию расширения
select pg1c.version();

-- Получаем версию и другую информацию о расширении из системного предстваления
select extversion,* from pg_extension where extname='pg1c';

-- Получаем информацию о проверке обновлений (новой версии)
-- При включенной настройке проверка осуществляется при получении метаданных
select check_updates,check_updates_timestamp from pg1c.server_1c;

-- Получаем изменения для таблицы 'Справочник.Контрагенты
select * from pg1c.log_metadata_sql where table_1c='Справочник.Контрагенты' order by 1;

-- Получаем изменения метаданных за последний час в обратном порядке
select * from pg1c.log_metadata_sql where timestamp>now()-interval '1 hour' order by 1 desc;

-- Удаляем из журнала все записи старше одного месяца
delete from pg1c.log_metadata_sql where timestamp<now()::date-interval '1 month';

-- Получаем ошибки (при настроенной записи исключений)
select * from pg1c.log_metadata_sql where exception is not null;

-- Получаем запросов к серверу УТ
select * from pg1c.log_http_request where server_1c='УТ' order by 1;

-- Получаем ТОП-10 самый длительных запросов
select * from pg1c.log_http_request order by duration desc limit 10;

-- Удаляем из журнала все записи старше одной недели
delete from pg1c.log_http_request where timestamp<now()::date-interval '1 week';

-- Получаем ошибки (при настроенной записи исключений)
select * from pg1c.log_http_request where exception is not null;