sql - 如果表不存在，如何正确选择

Question

我想从给定的表中运行 SELECT，如果该表不存在则应该不会失败。假设，为简单起见，该表仅包含一列和一行，而我在该标量之后。如果该表不存在，我想返回 0。

我知道我可以这样做:

query("select count(1) from information_schema.tables where table_name = 'versions'")
if result > 0
  return query("select version from versions limit 1")
else
  return 0

或者我可以使用存储过程/plpgsql 函数来做同样的事情。

但是有没有办法在单个临时查询中执行相同的操作？

(我正在做两个查询。我没有充分的理由改变它。我只是好奇是否/如何做。)

Answer 1

您希望使用纯 SQL 解决方案与服务器进行单次往返。这些年来，有许多相关的请求。这原则上是不可能的。

• 您或者需要动态 SQL - 这需要使用 PL/pgSQL 或其他 PL 的函数。
• 或者您需要两次 到服务器的往返行程。先查存在，后查询。

您也不能将它嵌套在一个普通的 SQL 函数中，它会在执行前计划主体中的每个语句，并在尝试解析不存在的表名时失败。它甚至不会在函数创建时通过表面测试。见:

• Why can PL/pgSQL functions have side effect, while SQL functions can't?
• Syntax error in dynamic SQL in pl/pgsql function

针对您的简单案例的两个简单解决方案(在许多可能的方法中):

CREATE OR REPLACE FUNCTION f_select_version_if_exists1() 
  RETURNS int LANGUAGE plpgsql PARALLEL SAFE AS 
$func$
BEGIN 
   IF EXISTS (
      SELECT FROM pg_catalog.pg_tables
      WHERE  tablename  = 'versions'
      AND    schemaname = 'public'  -- see below!
      ) THEN
      RETURN (SELECT version FROM versions LIMIT 1);
   ELSE
      RETURN 0;
   END IF;
END 
$func$;

或者:

CREATE OR REPLACE FUNCTION f_select_version_if_exists2(INOUT _version int = 0) AS 
$func$
BEGIN 
   IF EXISTS (
      SELECT FROM pg_catalog.pg_tables
      WHERE  tablename  = 'versions'
      AND    schemaname = 'public'  -- see below!
      ) THEN
      SELECT INTO _version version
      FROM   versions LIMIT 1;
   END IF;
END 
$func$  LANGUAGE plpgsql PARALLEL SAFE;

我强烈建议同时确定架构名称。表名在 Postgres 中不是唯一的。见:

• How to check if a table exists in a given schema

此外，我制作了函数 PARALLEL SAFE (仅当它可能嵌套在大 查询中时才重要)。这在依赖 search_path 时是错误的，因为它通常包含临时对象的架构，这使其成为PARALLEL UNSAFE

第二个是创造性地使用 INOUT 参数。相关:

• Can I make a plpgsql function return an integer without using a variable?