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 FROM orders) THEN
      DELETE FROM orders;
   ELSE
      INSERT INTO orders VALUES (1,2,3);
   END IF;
END
$do$

标准 SQL 中没有过程元素.IF 语句 是默认过程语言 PL/pgSQL 的一部分.您需要使用 DO 创建函数或执行临时语句 命令.

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 semicolon (;) at the end of each statement in plpgsql (except for the final END).

IF 语句的末尾需要 END IF;.

子选择必须用括号括起来:

A sub-select must 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 FROM orders) ...

替代方案

不需要额外的 SELECT.这样做也一样，而且速度更快:

Alternative

The additional SELECT is not needed. This does the same, faster:

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

尽管不太可能，写入同一个表的并发事务可能会干扰.为了绝对确定，write-lock 之前在同一个事务中的表按照演示进行.

Though unlikely, concurrent transactions writing to the same table may interfere. To be absolutely sure, write-lock the table in the same transaction before proceeding as demonstrated.

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