CODEWITHSUNDEEP
postgresql
Monkey Boson
Monkey Boson

Reputation: 1292

Index Results of Postgres Table Function

I'm looking to create a Postgres function that caches the results of a long-running query so it only needs to be executed once per transaction:

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';

Now, I can run all of my queries and join this function:

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

I've got a whole pile of these, without guarantees on which one will run first or in what order.

This works pretty well, except that the primary key index on the temporary table isn't used.

How can I return a "table" from a Postgres function, instead of just the results of a query?

  1. I'm using a function to build the temporary table instead of a materialized view to get around the "where clause doesn't get pushed into view that contains aggregation" issue.
  2. I could create the temporary table, then refer to it directly in all the queries, but it would mean having to build in some sort of blocking mechanism to ensure the queries aren't executed too early, and the tool I'm using doesn't support such mechanisms very well.

Upvotes: 4

Views: 1617

Answers (1)

clemens
clemens

Reputation: 17722

You can try the modifier STABLE which indicates that

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.

The newer Postgres versions support materialized views as well. You can create a materialized view for the join. AFAIK materialized views supports indices as well.

Upvotes: 3

Related Questions