CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE SCHEMA IF NOT EXISTS waybills;
-- triggers
CREATE OR REPLACE FUNCTION set_update()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
NEW.increment = NEXTVAL(TG_ARGV[0]);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TABLE IF NOT EXISTS waybills.contractor_integrations (
park_contractor_profile_id TEXT NOT NULL,
provider TEXT NOT NULL,
external_id TEXT,
meta JSONB,
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
increment BIGSERIAL NOT NULL,
PRIMARY KEY (park_contractor_profile_id, provider)
);
CREATE INDEX IF NOT EXISTS idx__contractors_park_contractor_profile_id ON waybills.contractor_integrations (park_contractor_profile_id);
DROP TRIGGER IF EXISTS trigger_update
ON waybills.contractors;
CREATE TRIGGER trigger_update BEFORE UPDATE ON waybills.contractor_integrations
FOR EACH ROW EXECUTE PROCEDURE set_update('waybills.contractor_integrations_increment_seq');
CREATE TYPE waybills.subscription_type AS ENUM ('personal', 'park');
CREATE TABLE IF NOT EXISTS waybills.subscriptions (
provider TEXT NOT NULL,
external_id TEXT NOT NULL,
subscription_type waybills.subscription_type NOT NULL,
expires_at TIMESTAMPTZ,
meta JSONB,
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
increment BIGSERIAL NOT NULL,
PRIMARY KEY (provider, external_id)
);
DROP TRIGGER IF EXISTS trigger_update
ON waybills.subscriptions;
CREATE TRIGGER trigger_update BEFORE UPDATE ON waybills.subscriptions
FOR EACH ROW EXECUTE PROCEDURE set_update('waybills.subscriptions_increment_seq');
INSERT INTO waybills.contractor_integrations (park_contractor_profile_id, provider, external_id)
VALUES
('1_1', 'aaaa', 'aaa1'),
('1_2', 'aaaa', 'aaa2'),
('1_1', 'bbbb', 'bbb1'),
('1_3', 'aaaa', null);
select * from waybills.contractor_integrations;
insert into waybills.subscriptions (provider, external_id, subscription_type, is_deleted)
values
('aaaa', 'aaa1', 'park', false),
('aaaa', 'aaa2', 'personal', false),
('bbbb', 'bbb1', 'personal', true);
with ci as (
select provider, external_id from waybills.contractor_integrations where park_contractor_profile_id = '1_1' and not is_deleted
), s as (
select provider, external_id, (subscription_type, expires_at) as subscription from waybills.subscriptions where (provider, external_id) in (select provider, external_id from ci) and not is_deleted
) select
ci.provider,
ci.external_id,
s.subscription
from ci left join s on ci.provider = s.provider and ci.external_id = s.external_id
Telegram: DB developers channel