How to re-use the result of a subquery in the main query, with Postgres

Thomas Published at Dev

Thomas

Here is a crude example of the problem I'm trying to solve:

SELECT
    (array_agg(ts))[1] as ts,
    (array_agg(ticker))[1] as ticker,
    round(avg(m1)::decimal,  (SELECT price_tick_size FROM exchange_debug.instruments WHERE ticker = 'BTCUSDT')) as m1,
    round(avg(m5)::decimal,  (SELECT price_tick_size FROM exchange_debug.instruments WHERE ticker = 'BTCUSDT')) as m5,
    round(avg(m15)::decimal, (SELECT price_tick_size FROM exchange_debug.instruments WHERE ticker = 'BTCUSDT')) as m15,
    round(avg(m30)::decimal, (SELECT price_tick_size FROM exchange_debug.instruments WHERE ticker = 'BTCUSDT')) as m30,
    ...

I need to know the rounding value for the data, load it once and reuse it everywhere.

I thought about using 'USING' above the query to get the rounding value:

WITH tick_size AS
    (SELECT price_tick_size FROM exchange_debug.instruments WHERE ticker = 'BTCUSDT')
SELECT
    (array_agg(ts))[1] as ts,
    (array_agg(ticker))[1] as ticker,
    round(avg(m1)::decimal, tick_size.price_tick_size),
    round(avg(m5)::decimal, tick_size.price_tick_size),
    ...

But then I get the following error:

missing FROM-clause entry for table "tick_size"

So, I can do this:

round(avg(m1)::decimal, (SELECT price_tick_size FROM tick_size)) as m1,

But I can't help to think there has to be something simpler.

Bergi

You can put the subquery in the FROM clause of your main query, as long as it is guaranteed to return exactly a single row:

SELECT
    (array_agg(ts))[1] as ts,
    (array_agg(ticker))[1] as ticker,
    round(avg(m1)::decimal, tick_size.price_tick_size),
    round(avg(m5)::decimal, tick_size.price_tick_size),
    ...
FROM
    ...,
    (SELECT price_tick_size FROM exchange_debug.instruments WHERE ticker = 'BTCUSDT') AS tick_size

Collected from the Internet

Please contact [email protected] to delete if infringement.