how to use correctly if statement postgresql

Question

I'm trying to create a trigger that populates a table everytime X amount of rows are added to other table. The logic would be:

1- check how many rows are in table A based on last date on table B.
2- once there are 1000 rows in table A from the last date on table B, add one row to table B

Table A stores ticks from market and table B stores OHLC data (Open, High, Low, Close). Ticks have only 3 columns: date, bid and ask.

Open is the first known price, Low is the min price, High is the max price and Close is the last price. All this prices are calculated based on the last 1000 ticks using the bid column. So it would be:

Open: bid n°0
High: max bid between 0 and 999
Low: min bid between 0 and 999
Close: bid n° 999

The thing is that I'm trying to check if there are enough ticks to create a candle, and I'm trying to construct the trigger like this:

with total_ticks as (
    select count(*) from (
    select *  from eurusd_tick2 eurusd where date > 
        (SELECT date from eurusd_ohlc order by date desc limit 1) 
        order by date asc) totals)

This code actually gives me the total amount of ticks that are from the last known date. The problem comes here, because I'm trying to use "if" conditionals in order to start building the logic, but I only get syntax errors.

For example, I have tried

if 1000<total_ticks then 
   raise notice 'there are less than 1000'

or

if 1000 < select * from total_ticks then
   raise notice 'there are less than 1000'

How should I use the if statement? what I'm missing here? I have also tried something like:

DO $$
DECLARE
  a integer := 10;
  b integer := 20;
BEGIN 
  IF a > b THEN
    RAISE NOTICE 'a is greater than b';
  END IF;

  IF a < b THEN
    RAISE NOTICE 'a is less than b';
  END IF;

  IF a = b THEN
    RAISE NOTICE 'a is equal to b';
  END IF;
END $$;

But I get errors that says that there is an error near or at "DO".

I'm confused, so I would appreciate a little help on how to use this.

Why would you do this with a loop or trigger when you can just run a query? — Gordon Linoff
Hi @GordonLinoff, correct me if I'm wrong, but I'm trying to build an actual trading system. The system is operated via python and I think its less resource consuming to have a trigger rather than an infinite loop with a python script that creates the candles when enough ticks arrives. I am correct? — Pedro Pablo Severin Honorato
Triggers add overhead to inserts and updates. I don't know what you need to optimize for, but my bias is toward post processing using a query rather than using triggers -- unless they really are necessary. — Gordon Linoff
Thats something I want to do, @GordonLinoff. I have one table that stores raw ticks and other table that stores the OHLC bars or candles. I have to add one new bar every 1000 new ticks. The ticks arrives at real time and in 1 second I could have 3 new ticks, 10 new ticks or 0 new ticks. Thats why I think a trigger would do a better job rather than a python script. — Pedro Pablo Severin Honorato

Abelisto Abelisto · Accepted Answer · 2020-05-12T05:55:15

To make it shorter a bit:

do $$
declare
  a integer := 10;
  b integer := 20;
  msg text;
begin
  msg := case
    when a>b then 'a is greater than b'
    when a<b then 'a is less than b'
    when a=b then 'a is equal to b'
    else 'NaN here'
  end;
  raise notice '%', msg;
end $$;

It obviously works in psql. Maybe something wrong with finance trading candlestick-chart

how to use correctly if statement postgresql

1 Answers