如何检测Postgres临时表是否已存在？ [英] How can I detect if a Postgres temporary table already exists?
问题描述
我有一个名为 tester 的表，我需要在该表上覆盖一个临时表才能进行一些测试。到目前为止，我已经使用以下方法解决了这个问题：
I have a table called tester which I need to overlay with a temporary table in order to do some testing. So far I have managed that using:
CREATE TEMPORARY TABLE tester AS
SELECT * FROM tester WHERE name='bananas';
该临时表将在会话结束时消失，这对我来说很好，但这是负责连接的外部应用程序，它倾向于缓存连接以供重用（因此无法保证临时表已被删除）。这意味着，如果我再次尝试执行上述查询，则可能会失败，因为临时表仍然存在。
This temporary table will disappear at the end of the session which is fine by me, however it is the external application that is responsible for the connection and it has a tendency to cache connections for reuse (thus there is no guarantee that the temporary table has been dropped). This means that if I try to execute the above query again it may fail because the temporary table still exists.
显式删除临时表实际上不是一个选择。我宁愿接受失败的查询，也不愿冒险误删除底层的真实表。
Explicitly dropping the temporary table is not really an option. I'd rather live with the failed query than risk dropping the underlying "real" table by mistake.
我将要要做的是仅在临时表不存在时创建它。
我找到了这个查询，人们在检查表是否存在时建议使用该查询：
What I would like to do is only create the temporary table if it doesn't already exist. I did find this query which people suggested should be used when checking to see if a table exists:
SELECT * FROM pg_catalog.pg_class WHERE relkind = 'r' AND relname = 'tester';
result when temporary table exists:
relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass | relfrozenxid | relacl | reloptions
---------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------------+--------+------------
tester | 2200 | 1533065 | 1531747 | 0 | 1533063 | 0 | 0 | 0 | 1533067 | 0 | t | f | r | 3 | 0 | 0 | 0 | 0 | 0 | f | t | f | f | 17654031 | |
tester | 1533088 | 1533065 | 1531747 | 0 | 1533160 | 0 | 0 | 0 | 1533163 | 0 | f | f | r | 3 | 0 | 0 | 0 | 0 | 0 | f | f | f | f | 17654066 | |
如您所见，有两条记录。一个用于原始表，另一个用于临时表。第二条记录是临时表-我知道这是因为它在不同的会话中工作时会消失-但除了缺少索引或主键之外，这是我唯一可以识别为临时表的方法。这只是一张测试表-我正在使用的某些实际表可能缺少索引和/或主键。
As you can see, there are two records. One for the original table, and one for the temp table. The second record is the temporary table - I know this because it disappears when working in a different session - but other than the lack of an index or a primary key that's the only way I can identify it as being temporary. This is only a test table - some of the real tables I'm working with may lack indexes and/or primary keys.
问题（tl; dr版）：如果已经存在一个具有相同名称的真实表，我该如何测试临时表是否存在？（或至少确定该表是临时表？）
Question (tl;dr edition): How can I test if a temporary table exists if there is already a "real" table with the same name? (or at least identify that a table is temporary?)
免责声明：我添加了Java标记，因为这是我用来执行查询的内容（SQL只是应用程序的一小部分）。即使Java与问题没有直接关系，我还是需要从Java上下文中使用答案。
推荐答案
用户定义函数，用于检查临时表是否存在。
User Defined Function to check whether temp table exists.
CREATE or REPLACE FUNCTION public.iftableexists( varchar)
RETURNS pg_catalog.bool AS
$BODY$
DECLARE
BEGIN
/* check the table exist in database and is visible*/
perform n.nspname ,c.relname
FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid
= c.relnamespace
where n.nspname like 'pg_temp_%' AND pg_catalog.pg_table_is_visible(c.oid)
AND Upper(relname) = Upper($1);
IF FOUND THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
这篇关于如何检测Postgres临时表是否已存在？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
