sql - 作用于 CTE 结果的 PostgreSQL 函数/ View ？

Question

我正在编写一个 postgresql View ，它使用一些复杂的逻辑来生成数据行。它要求我对从查询两个不同表的结果派生的两组数据执行相同的一组步骤。是否可以编写一个概括这些步骤的函数？

例如:

CREATE TABLE foos (
  id bigserial primary key,
  name text not null
);

INSERT INTO foos (name) VALUES ('FOO_ONE');
INSERT INTO foos (name) VALUES ('FOO_TWO');
INSERT INTO foos (name) VALUES ('FOO_THREE');
INSERT INTO foos (name) VALUES ('FOO_FOUR');

CREATE TABLE bars (
  id bigserial primary key,
  name text not null
);

INSERT INTO bars (name) VALUES ('BAR_ONE');
INSERT INTO bars (name) VALUES ('BAR_TWO');
INSERT INTO bars (name) VALUES ('BAR_THREE');
INSERT INTO bars (name) VALUES ('BAR_FOUR');

现在我必须计算:

WITH even_foos AS (SELECT * FROM foos WHERE id % 2 = 0)
SELECT id * 2 AS double_id, name FROM even_foos;

和

WITH odd_bars AS (SELECT * FROM foos WHERE id % 2 = 1)
SELECT id * 2 AS double_id, name FROM odd_bars;

请注意 id * 2 选择在两个查询中是如何重复的。我想重用这个功能，这样我只需要实现一次这个逻辑。例如(组成语法):

WITH even_foos AS (SELECT * FROM foos WHERE id % 2 = 0)
SELECT * FROM get_double_id_and_name(even_foos);

和

WITH odd_bars AS (SELECT * FROM foos WHERE id % 2 = 1)
SELECT * FROM get_double_id_and_name(odd_bars);

有没有简单的方法可以做到这一点？

Answer 1

Dynamic SQL 肯定可以解决这个问题，因为您可以将 SQL 包装在一个循环中，循环是在包含 (0, 1 )，然后将其动态插入到查询中以获取 0 和 1 的值。

如果你想让它保持纯SQL，你可以把查询变成一个函数，像这样:

CREATE FUNCTION get_foos(int) RETURNS TABLE (id bigint, name text)
    AS $$ SELECT * FROM foos WHERE id % 2 = $1 $$
    LANGUAGE SQL;

然后这样调用它:

SELECT get_foos(1);
SELECT get_foos(0);

对于您提供的具体示例，尚不清楚该函数的开销是否值得，因为在这种情况下您将传递给它的值实际上只是一组 2 个值。一组潜在的参数值比这大得多，像我上面所做的那样将公共(public)代码分解成一个函数绝对是一个巨大的胜利。 (鉴于您提到了一个使用一些复杂逻辑生成数据行的 View ，听起来它很可能最终适合后一种情况，其中一个函数是值得的。)

当然，即使在这种情况下，将其放入函数中也很好，特别是如果您希望集中该逻辑。这种集中化还具有将逻辑封装在数据库中的好处，因此您可以通过 SQL 从一个 Python 程序和一个 Ruby< 中调用它 程序等，无需在每种情况下重复实际驱动程序代码中的逻辑。

这是一个 SQL fiddle，其中定义了函数并调用了它: http://sqlfiddle.com/#!15/fb079/3