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:
- code the SELECT directly into the function as above?
or
- 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.
