Re: Dynamically filtering a CTE?

On Fri, Apr 20, 2018 at 09:33:22AM -0700, David G. Johnston wrote: > On Fri, Apr 20, 2018 at 9:22 AM, W. Trevor King wrote: > > format(' > > WITH RECURSIVE _ancestors(id, ancestors) AS ( > > SELECT > > item.id AS id, > > ARRAY[item.ancestor_id] AS ancestors > >

Re: Dynamically filtering a CTE?

On Fri, Apr 20, 2018 at 9:22 AM, W. Trevor King wrote: > format(' > WITH RECURSIVE _ancestors(id, ancestors) AS ( > SELECT > item.id AS id, > ARRAY[item.ancestor_id] AS ancestors > FROM items AS item > %s > ​[...]​ > > ',

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()

Re: Dynamically filtering a CTE?

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.

Dynamically filtering a CTE?

I have a slow ‘WITH RECURSIVE’ CTE like: CREATE VIEW ancestors AS WITH RECURSIVE _ancestors(descendant, ancestors) AS ( SELECT item.id AS id, ARRAY[item.ancestor_id] AS ancestors FROM items AS item UNION ALL SELECT child.id AS id