Re: Dynamically filtering a CTE?

On Thu, Apr 19, 2018 at 05:28:00PM -0700, David G. Johnston wrote:
> On Thursday, April 19, 2018, W. Trevor King wrote:
> > Is there an idiomatic way to approach this problem?
>
> I would use pl/pgsql as the language and build a query using a
> combination of text literals and the format() function - invoking
> via pl/pgsql's EXECUTE command.

That works. I've ended up with:

CREATE OR REPLACE FUNCTION ancestors(condition text)
RETURNS TABLE(id integer, ancestors integer[]) AS
$$
BEGIN
RETURN QUERY EXECUTE format('
WITH RECURSIVE _ancestors(id, ancestors) AS (
SELECT
item.id AS id,
ARRAY[item.ancestor_id] AS ancestors
FROM items AS item
%s
UNION ALL
SELECT
descendant.id AS id,
descendant.ancestors || ancestor.ancestor_id AS ancestors
FROM _ancestors AS descendant
JOIN items as ancestor
ON descendant.ancestors[array_length(descendant.ancestors, 1)] = ancestor.id
)
SELECT
id,
ancestors[1:array_length(ancestors, 1) - 1] AS ancestors -- drop the trailing NULL
FROM _ancestors
WHERE ancestors[array_length(ancestors, 1)] IS NULL -- remove non-terminal recursion
', condition);
END
$$ LANGUAGE plpgsql STABLE;

which you can use like:

SELECT * FROM ancestors('WHERE item.id = 62324721');

or (without filtering, for the full, slow CTE):

SELECT * FROM ancestors('');

Thanks,
Trevor

--
This email may be signed or encrypted with GnuPG (http://www.gnupg.org).
For more information, see http://en.wikipedia.org/wiki/Pretty_Good_Privacy