如何正确使用if语句postgresql [英] how to use correctly if statement postgresql

问题描述

我正在尝试创建一个触发器，每当X数量的行添加到其他表时，该触发器就会填充一个表.逻辑将是:

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

表A存储市场报价，表B存储OHLC数据(开盘，高点，低点，收盘价).报价只有3栏:日期，出价和要价.

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.

开盘价是第一个已知价格，低价是最小价格，高价是最大价格，而平仓价是最后一个价格.所有这些价格都是根据使用出价列的最后1000个报价计算得出的.这样就可以了:

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:

• 打开:出价n°0
• 最高:最高出价在0到999之间
• 低:最低出价在0到999之间
• 关闭:出价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)

此代码实际上为我提供了自上次已知日期以来的滴答声总量.问题来了，因为我试图使用"if"条件语句来开始构建逻辑，但是我只会遇到语法错误.

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.

例如，我尝试过

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

或

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

我应该如何使用if语句?我在这里缺少什么?我也尝试过类似的操作:

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 $$;

但是我收到错误消息，提示"DO"附近或附近有错误.

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.

推荐答案

要简短一点:

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 $$;

它显然可以在 psql 中使用.金融交易烛台图

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

这篇关于如何正确使用if语句postgresql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！