CODEWITHSUNDEEP
postgresqlstored-procedurespostgresql-8.4greenplum
user2569524
user2569524

Reputation: 1751

Return result set from function

I have a query that is very slow and I wanted to place the query in a function and have the function return the result instead of running the query each time to improve the performance of the query.

Query: 

select a.*,b.first_column from 
test1 a left join 
test2 b on 
a.id=b.id

test2 table is fairly big 28505267 rows. test1 has about 1000 rows.

Is there a way to return the query result directly from postgres function with out having to define the type on returns setof or returns table?

Upvotes: 2

Views: 1657

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656381

Is there a way to return the query result directly from postgres function without having to define the type as returns setof or returns table?

No. But your function can just as well be a VIEW (close to zero difference in performance), which can do that:

CREATE VIEW vw_t1_t2 AS
SELECT a.*, b.first_column
FROM   test1 a
LEFT   JOIN test2 b USING (id);

The key to make this function halfway fast is an index on table2.id, which I can only assume exists. Since you only retrieve a single column from the big table2 a multicolumn index on (id, first_column) could go a long way, allowing index-only scans - in Postgres 9.2, not in Greenplum (yet).

If you insist on creating a function, you either have to spell out all columns of table1 plus first_column from table2 in a RETURNS TABLE(...) clause, or you base the return type on the row type of the view (which you need to create first and keep).:

CREATE OR REPLACE FUNCTION f_t1_t2 (integer)
  RETURNS SETOF vw_t1_t2 AS
$func$
SELECT a.*, b.first_column
FROM   test1 a
LEFT   JOIN test2 b USING (id)
WHERE  a.id = $1
$func$ LANGUAGE sql;

I added a function parameter to make the example halfway useful. Your question isn't really enlightening where you are headed.

If you really want to materialize the result of the query for reuse, you need to create another table with a snapshot of the result. That's effectively a MATERIALIZED VIEW, but that was institutionalize in Postgres 9.3 and Greenplum does not have that (yet). But you can manage your snapshot table manually ...

Upvotes: 1

Related Questions