SQLize Online / PHPize Online  /  SQLtest Online

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 NOT NULL, 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'); select * from waybills.contractor_integrations; with ci as ( select ci.provider, ci.external_id, s.subscription_type, s.expires_at from waybills.contractor_integrations as ci left join waybills.subscriptions as s on ci.provider = s.provider and ci.external_id = s.external_id where ci.park_contractor_profile_id='1_1' and not ci.is_deleted;