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?
- 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.