SQLize Online / PHPize Online  /  SQLtest Online

do $$ begin if not exists (select * from pg_type where typname = 'duplicate_strategy') THEN create type duplicate_strategy as enum ('UPDATE', 'SKIP', 'ROLLBACK'); end if; if not exists (select * from pg_type where typname = 'operation_type') THEN create type operation_type as enum ('INSERT', 'UPDATE', 'UPSERT', 'DELETE'); end if; if not exists (select * from pg_type where typname = 'processed_state') THEN create type processed_state as enum ('SUCCESS', 'ERROR'); end if; end $$; create table if not exists trans_entity_request ( id uuid primary key not null default uuid_generate_v4(), trans_id uuid not null, oper_type_code operation_type not null, duplicate_strategy_code duplicate_strategy, request_body jsonb not null, processed_entity_cnt integer not null default 0, insert_entity_cnt integer not null default 0, update_entity_cnt integer not null default 0, delete_entity_cnt integer not null default 0, error_entity_cnt integer not null default 0, processed_state_code processed_state ); create or replace function trans_entity_request_ins( p_trans_id uuid, p_oper_type_code varchar, p_duplicate_strategy_code varchar, p_request_body jsonb) returns uuid language sql volatile security definer parallel unsafe as $body$ insert into trans_entity_request ( id, trans_id, oper_type_code, duplicate_strategy_code, request_body ) values ( uuid_generate_v4(), $1, --p_trans_id cast ($2 as operation_type), --p_oper_type_code cast ($3 as duplicate_strategy), --p_duplicate_strategy_code $4 --p_request_body ) returning id; $body$; select * from trans_entity_request_ins( p_trans_id => '4f025e68-39fb-4414-81e6-bfaeb5566c1e', p_oper_type_code => 'INSERT', p_duplicate_strategy_code => null, p_request_body => '[]'::jsonb)