clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36251 in the last week).

CREATE TABLE asset ( id serial PRIMARY KEY, name character varying(16) unique, precision smallint, commission_precision smallint, created_at TIMESTAMPTZ );
 hidden batch(es)


CREATE TABLE exchange ( id serial PRIMARY KEY, name character varying(32) unique, created_at TIMESTAMPTZ );
 hidden batch(es)


CREATE TABLE symbol ( id serial primary key, name character varying(32), exchange_id int REFERENCES exchange(id), base_asset_id int REFERENCES asset (id), quote_asset_id int REFERENCES asset (id), created_at TIMESTAMPTZ, unique (name, exchange_id) );
 hidden batch(es)


CREATE TYPE symbol_record AS ( name character varying(32), exchange character varying(64), base_name character varying(16), base_precision smallint, base_commission_precision smallint, quote_name character varying(16), quote_precision smallint, quote_commission_precision smallint );
 hidden batch(es)


CREATE OR REPLACE FUNCTION fn_insert_symbol_record(VARIADIC _val symbol_record[]) RETURNS void LANGUAGE sql AS $func$ WITH val AS (SELECT * FROM unnest(_val)), typ_asset AS (SELECT v.name, v.precision, v.commission_precision, a.id FROM ( SELECT DISTINCT ON (base_name) name, (base_precision) "precision", (base_commission_precision) commission_precision FROM val UNION SELECT DISTINCT ON (quote_name) name, (quote_precision) "precision", (quote_commission_precision) commission_precision FROM val ) v LEFT JOIN asset a USING (name) ), typ_exch AS (SELECT v.name, e.id FROM ( SELECT DISTINCT exchange name FROM val ) v LEFT JOIN exchange e USING (name) ), ins_asset AS (INSERT INTO asset AS a (name, "precision", commission_precision, created_at) SELECT name, "precision", commission_precision, now() FROM typ_asset WHERE id IS NULL ON CONFLICT (name) DO UPDATE SET created_at = EXCLUDED.created_at WHERE a.created_at <> EXCLUDED.created_at RETURNING a.name, a.id ), ins_exch AS (INSERT INTO exchange AS e (name, created_at) SELECT name, now() FROM typ_exch WHERE id IS NULL ON CONFLICT (name) DO UPDATE SET name = EXCLUDED.name RETURNING e.name, e.id ) INSERT INTO symbol AS s (name, exchange_id, base_asset_id, quote_asset_id, created_at) SELECT v.name, COALESCE(te.id, ie.id), COALESCE(tb.id, ib.id), COALESCE(tq.id, iq.id), now() FROM val v LEFT JOIN typ_asset tb ON tb.name = v.base_name LEFT JOIN ins_asset ib ON ib.name = v.base_name LEFT JOIN typ_asset tq ON tq.name = v.quote_name LEFT JOIN ins_asset iq ON iq.name = v.quote_name LEFT JOIN typ_exch te ON te.name = v.exchange LEFT JOIN ins_exch ie ON ie.name = v.exchange ON CONFLICT (name, exchange_id) DO UPDATE SET base_asset_id = EXCLUDED.base_asset_id WHERE s.base_asset_id <> EXCLUDED.base_asset_id $func$;
 hidden batch(es)


SELECT fn_insert_symbol_record( ('ETHBTC', 'Binance', 'ETH', 5::smallint, 5::smallint, 'BTC', 3::smallint, 3::smallint), ('ETHUSDT', 'Binance', 'ETH', 5::smallint, 5::smallint, 'USDT', 4::smallint, 4::smallint), ('BTCUSDT', 'Binance', 'BTC', 3::smallint, 3::smallint, 'USDT', 4::smallint, 4::smallint) );
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values. CONTEXT: SQL function "fn_insert_symbol_record" statement 1
 hidden batch(es)


TABLE asset;
id name precision commission_precision created_at
 hidden batch(es)


TABLE exchange;
id name created_at
 hidden batch(es)


TABLE symbol;
id name exchange_id base_asset_id quote_asset_id created_at
 hidden batch(es)