clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36248 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, a.id FROM ( SELECT DISTINCT (base_name) name FROM val UNION SELECT DISTINCT (quote_name) name 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, created_at) SELECT name, now() FROM typ_asset WHERE id IS NULL ON CONFLICT (name) DO UPDATE SET name = EXCLUDED.name WHERE a.name <> EXCLUDED.name 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) );
fn_insert_symbol_record
 hidden batch(es)


TABLE exchange;
id name created_at
1 Binance 2022-02-14 19:13:19.108725+00
 hidden batch(es)


TABLE asset;
id name precision commission_precision created_at
1 ETH 2022-02-14 19:13:19.108725+00
2 BTC 2022-02-14 19:13:19.108725+00
3 USDT 2022-02-14 19:13:19.108725+00
 hidden batch(es)


TABLE symbol;
id name exchange_id base_asset_id quote_asset_id created_at
1 ETHBTC 1 1 2 2022-02-14 19:13:19.108725+00
2 BTCUSDT 1 2 3 2022-02-14 19:13:19.108725+00
3 ETHUSDT 1 1 3 2022-02-14 19:13:19.108725+00
 hidden batch(es)