postgresql - Postgres表函数的索引结果

Question

我想创建一个 Postgres 函数来缓存长时间运行的查询的结果，因此每个事务只需要执行一次:

CREATE OR REPLACE FUNCTION get_records (
  _state VARCHAR(10)
)
RETURNS TABLE (
  id UUID
) AS
$$
DECLARE
  _temptable VARCHAR;
BEGIN
  _temptable := FORMAT('temp_state_%s', _state);

  IF NOT EXISTS(SELECT 1 FROM pg_tables WHERE tablename = _temptable) THEN
    EXECUTE FORMAT('CREATE TEMPORARY TABLE %I (id UUID NOT NULL, PRIMARY KEY(_uid)) ON COMMIT DROP', _temptable);
    EXECUTE FORMAT('INSERT INTO %I SELECT id FROM very_complex_nested_query', _temptable);
    EXECUTE FORMAT('ANALYZE %I', _temptable);
  END IF;

  RETURN QUERY EXECUTE FORMAT('SELECT id FROM %I', _temptable);
END;
$$
LANGUAGE 'plpgsql';

现在，我可以运行我所有的查询并加入这个函数:

SELECT mt.*
FROM my_table1 mt
  INNER JOIN get_records('new') nr ON mt.id = nr.id

SELECT mt.*
FROM my_table2 mt
  INNER JOIN get_records('new') nr ON mt.id = nr.id

SELECT mt.*
FROM my_table3 mt
  INNER JOIN get_records('new') nr ON mt.id = nr.id

-- ... many more

我有一大堆这些，不能保证哪个先运行或以什么顺序运行。

除了没有使用临时表上的主键索引外，这工作得很好。

我如何从 Postgres 函数返回“表”，而不仅仅是查询的结果？

1. 我正在使用一个函数来构建临时表而不是物化 View 来解决“where clause doesn't get pushed into view that contains aggregation”问题。
2. 我可以创建临时表，然后在所有查询中直接引用它，但这意味着必须构建某种阻塞机制以确保查询不会过早执行，而我正在使用的工具using 不能很好地支持此类机制。

Answer 1

你可以试试修饰符STABLE这表明

STABLE indicates that the function cannot modify the database, and that within a single table scan it will consistently return the same result for the same argument values, but that its result could change across SQL statements.

较新 Postgres 版本也支持物化 View 。您可以为联接创建物化 View 。 AFAIK 物化 View 也支持索引。