未终止的美元报价 [英] Unterminated dollar quote

问题描述

如何在PostgreSql(11版)中使用IF语句?我只尝试了原始 IF 用法，但遇到了问题(IF"处或附近的语法错误).为了解决这个问题，人们建议使用do &&"但它不起作用(未终止的美元报价从 SQL DO $$ BEGIN IF ......中的位置 3 开始).这是我的 SQL 代码:

How to use IF statement in the PostgreSql (11 version)? I tried just raw IF usage but got problem (syntax error at or near "IF"). To resolve this problem people propose to use 'do &&' but it does not work as well (Unterminated dollar quote started at position 3 in SQL DO $$ BEGIN IF ......). Here is my SQL code:

DO $$
BEGIN
  IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'categories')) THEN
    CREATE TABLE IF NOT EXISTS categories
    (
      id   SERIAL NOT NULL,
      name character varying(40),
      CONSTRAINT categories_pkey PRIMARY KEY (id)
    );

    INSERT INTO categories (name) VALUES ('Games');
    INSERT INTO categories (name) VALUES ('Multimedia');
    INSERT INTO categories (name) VALUES ('Productivity');
    INSERT INTO categories (name) VALUES ('Tools');
    INSERT INTO categories (name) VALUES ('Health');
    INSERT INTO categories (name) VALUES ('Lifestyle');
    INSERT INTO categories (name) VALUES ('Other');
  END IF;
END
$$;

如果表不存在，我只需要创建表并插入一些初始化数据.

All I need is to create table and insert some init data to it if table does not exist.

推荐答案

某些平台不支持美元报价.在您的具体示例中，您应该在最后一个 END 后面有一个分号.您可能还需要添加 DECLARE 语句.

Some platforms do not support dollar quoting. In your specific example you should have a semicolon after the last END. You may need to add a DECLARE statement also.

DO
$$
DECLARE
BEGIN
    IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'categories')) 
    THEN
        CREATE TABLE IF NOT EXISTS categories
        (
          id   SERIAL NOT NULL,
          name character varying(40),
          CONSTRAINT categories_pkey PRIMARY KEY (id)
        );

        INSERT INTO categories (name) VALUES ('Games');
        INSERT INTO categories (name) VALUES ('Multimedia');
        INSERT INTO categories (name) VALUES ('Productivity');
        INSERT INTO categories (name) VALUES ('Tools');
        INSERT INTO categories (name) VALUES ('Health');
        INSERT INTO categories (name) VALUES ('Lifestyle');
        INSERT INTO categories (name) VALUES ('Other');
    END IF;
END;
$$  LANGUAGE PLPGSQL;

对于无法识别美元引用的平台，您可以使用 ' 代替.不过，您需要对匿名函数主体中的任何 ' 进行转义.

For platforms that don't recognize dollar quoting you can use ' instead. You'll need to escape any ' in the body of the anonymous function though.

像这样:

DO
'
DECLARE
BEGIN
    IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''categories'')) 
    THEN
        CREATE TABLE IF NOT EXISTS categories
        (
          id   SERIAL NOT NULL,
          name character varying(40),
          CONSTRAINT categories_pkey PRIMARY KEY (id)
        );

        INSERT INTO categories (name) VALUES (''Games'');
        INSERT INTO categories (name) VALUES (''Multimedia'');
        INSERT INTO categories (name) VALUES (''Productivity'');
        INSERT INTO categories (name) VALUES (''Tools'');
        INSERT INTO categories (name) VALUES (''Health'');
        INSERT INTO categories (name) VALUES (''Lifestyle'');
        INSERT INTO categories (name) VALUES (''Other'');
    END IF;
END;
'  LANGUAGE PLPGSQL;

DBFiddle 查看工作示例.

这篇关于未终止的美元报价的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！