Re: Dynamically filtering a CTE? - Mailing list pgsql-general

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