Postgresql – Postgres plpgsql: Is a view better than a select query in a plpgsql function

plpgsqlpostgresqlrecursive

I have a recursive query to the general effect of:

WITH RECURSIVE text1 (selfid, parentid, text) AS 
(
SELECT DISTINCT
  text.selfid,
  text.parentid,
  text.text
FROM
  text
WHERE
  text.selfid = entryPoint
UNION ALL
SELECT
  text.selfid,
  text.parentid,
  text.text
FROM
  text,
  text1
WHERE
  text1.selfid = text.parentid
) 
SELECT
  *
FROM
  text1
ORDER BY
  text1.selfid;

I'm using this in a plpgsql function where the value entryPoint is the starting point of the recursion and is passed in.

Question: In the plpgsql, should I:

  1. code the SELECT directly into the function as above?

or

  1. programatically create a VIEW in the pl/sql function, then get my result set from the newly created VIEW?

Some of the queries will get very large result sets, sometimes as many as 500K rows (up to 4GB in the total result set). I wonder if a view would have better performance than a select.

Best Answer

There will be no difference in performance between a view and a straight-up query. View is basically a saved query text. If you expect to call this exact query in multiple places in your code, it may make sense to create a view. Otherwise, put a query in your pgplsql code, it will improve readability of it.

Related Question