PostgreSQL - 执行SELECT时检查外键是否存在 [英] PostgreSQL - Check foreign key exists when doing a SELECT

问题描述

假设我有以下数据：
$ b $ some_table ：

  some_table_id |值| other_table_id 
 -------------------------------------- 
 1 | foo | 1 
 2 |酒吧| 2 
  

表 other_table ：

  other_table_id |值
 ---------------------- 
 1 | foo 
 2 | bar 
  

这里， some_table 有一个外键将 other_table_id 从 other_table 列到某个名称的列中。

在PostgreSQL中使用以下查询：

pre $ SELECT
FROM some_table $ b $ WHERE other_table_id = 3 ;

如您所见， 3 不存在在 other_table中这个查询显然会返回0个结果。
$ b

不进行第二个查询 ，有没有办法知道我的过滤器有效使用的外键不存在于 other_table ？

理想情况下，可能会解析错误（因为它在执行 INSERT 或 UPDATE 时发生）例如一个错误的外键）。

解决方案

你可以利用PL / pgSQL的一个特性来实现这个
$ b

  CREATE OR REPLACE FUNCTION f_select_from_some_tbl（int）
  RETURNS SETOF some_table AS 
 $ func $ 
 BEGIN 
 RETURN QUERY 
 SELECT * 
 FROM some_table 
 WHERE other_table_id = $ 1; 
 
如果没有找到，则
 RAISE WARNING'使用不存在的other_table_id>>调用％<<'，$ 1; 
 END IF; 
 END 
 $ func $ LANGUAGE plpgsql; 
  

最后一个 RETURN; 是可选的如果您的查询没有返回任何行，则仅会引发 WARNING 。 我在示例中没有提出 ERROR ，因为这会回滚整个事务（但如果它符合您的需要，可以这样做）。

我们已经添加了一个代码示例与Postgres 9.3的手册 来证明这一点。

Suppose I have the following data:

Table some_table:

some_table_id | value | other_table_id
--------------------------------------
1             | foo   | 1
2             | bar   | 2

Table other_table:

other_table_id | value
----------------------
1              | foo
2              | bar

Here, some_table has a foreign key to column other_table_id from other_table into the column of some name.

With the following query in PostgreSQL:

SELECT * 
FROM some_table 
WHERE other_table_id = 3;

As you see, 3 does not exists in other_table This query obviously will return 0 results.

Without doing a second query, is there a way to know if the foreign key that I am using as a filter effectively does not exist in the other_table?

Ideally as an error that later could be parsed (as it happends when doing an INSERT or an UPDATE with a wrong foreign key, for example).

解决方案

You can exploit a feature of PL/pgSQL to implement this very cheaply:

CREATE OR REPLACE FUNCTION f_select_from_some_tbl(int)
  RETURNS SETOF some_table AS
$func$
BEGIN
   RETURN QUERY
   SELECT * 
   FROM   some_table 
   WHERE  other_table_id = $1;

   IF NOT FOUND THEN
      RAISE WARNING 'Call with non-existing other_table_id >>%<<', $1;
   END IF;
END
$func$  LANGUAGE plpgsql;

A final RETURN; is optional in this case.

The WARNINGis only raised if your query doesn't return any rows. I am not raising an ERROR in the example, since this would roll back the whole transaction (but you can do that if it fits your needs).

We've added a code example to the manual with Postgres 9.3 to demonstrate this.

这篇关于PostgreSQL - 执行SELECT时检查外键是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！