PostgreSQL IF语句 [英] PostgreSQL IF statement

问题描述

如何在Postgres中进行此类查询？

How can I do such query in Postgres?

IF (select count(*) from orders) > 0
THEN
  DELETE from orders
ELSE 
  INSERT INTO orders values (1,2,3);

推荐答案

尝试：

DO
$do$
BEGIN
IF EXISTS (SELECT 1 FROM orders) THEN
   DELETE FROM orders;
ELSE 
   INSERT INTO orders VALUES (1,2,3);
END IF;
END
$do$

主要积分

• 标准SQL中没有过程元素。 IF 声明是默认过程语言PL / pgSQL的一部分。您需要使用 DO创建函数或执行ad-hoc语句 命令。

  Major points

  • There are no procedural elements in standard SQL. The IF statement is part of the default procedural language PL/pgSQL. You need to create a function or execute an ad-hoc statement with the DO command.

    您需要; plpgsql中每个语句的结尾（除了最终的 END ）。

    You need a ; at the end of each statement in plpgsql (except for the final END).

    您需要 END IF; 在结尾处 IF 声明。

    子选择需要用括号括起来：

    A sub-select needs to be surrounded by parentheses:

    IF (SELECT count(*) FROM orders) > 0 ...
    

    或者：

    IF (SELECT count(*) > 0 FROM orders) ...
    

    这相当且速度更快，但是：

    This is equivalent and much faster, though:

    IF EXISTS (SELECT 1 FROM orders) ...
    

  
  

  这里你根本不需要额外的 SELECT 。这样做也快一点：

  You do not actually need an additional SELECT at all here. This does the same, a bit faster:

  DO
  $do$
  BEGIN
  DELETE FROM orders;
  IF NOT FOUND THEN
     INSERT INTO orders VALUES (1,2,3);
  END IF;
  END
  $do$
  

  这篇关于PostgreSQL IF语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！