sql - 来自反斜杠的 PostgreSQL 语法错误？

Question

我在 PostgreSQL 的 pgAdminIII 中调试一个 sql 文件，这个语句在执行时产生语法错误:

ERROR:  syntax error at or near "v"
LINE 81384: ...n.oid = c.relnamespace  WHERE     c.relkind IN (\'v\') AND n...

声明:

CREATE OR REPLACE FUNCTION getnextview()   RETURNS name AS ' DECLARE   my_record RECORD;  viewName name; BEGINFOR my_record IN  SELECT c.relname  FROM pg_catalog.pg_class AS c  LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace  WHERE c.relkind IN (\'v\')  AND n.nspname NOT IN (\'pg_catalog\', \'pg_toast\')  AND pg_catalog.pg_table_is_visible(c.oid)  LIMIT 1LOOP   viewName := my_record.relname;  END LOOP;RETURN (viewName);END; '  LANGUAGE 'plpgsql' VOLATILE;

请注意，pg_catalog 和 pg_toast 也会出错。

Answer 1

所以完整的陈述确实大大改变了画面。

你的问题是单引号的嵌套。如果您使用 PostgreSQL 的“dollar quoting”，一切都会容易得多:

CREATE OR REPLACE FUNCTION getnextview() 
  RETURNS name 
AS 
$body$
DECLARE 
    my_record RECORD; 
    viewName name; 
BEGIN 
    FOR my_record IN 
            SELECT c.relname 
            FROM pg_catalog.pg_class AS c 
              LEFT JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace 
            WHERE c.relkind IN ('v') 
              AND n.nspname NOT IN ('pg_catalog', 'pg_toast') 
              AND pg_catalog.pg_table_is_visible(c.oid) 
            LIMIT 1 
    LOOP 
        viewName := my_record.relname; 
    END LOOP; 

    RETURN (viewName); 
END; 
$body$
LANGUAGE 'plpgsql' 
VOLATILE;