From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Doug Kyle <dkyle(at)grpl(dot)org> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Recursive CTE in function problem |
Date: | 2016-02-04 19:45:48 |
Message-ID: | CAKFQuwZ4eGOKPUZt3HToA=SA=e=2wLK8uAfP_2f62WcE-hHowQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Feb 4, 2016 at 12:37 PM, Doug Kyle <dkyle(at)grpl(dot)org> wrote:
> But when I use it as a function it always returns false:
>
> CREATE OR REPLACE FUNCTION grpl_collection.copy_in_collection_name(cpid
> bigint, colname text)
> RETURNS boolean
> LANGUAGE plpgsql
> AS $function$
> BEGIN
> RETURN cpid in (select copy from grpl_collection.collections_copy_map
> where copy=cpid and collection in
> (WITH RECURSIVE
> q AS
> (
> SELECT c.id
> FROM grpl_collection.collections c
> WHERE name=$$colname$$
> <<<------------
>
> UNION
> SELECT cn.id
> FROM q
> JOIN grpl_collection.collections cn
> ON cn.parent = q.id
> )
> SELECT id FROM q ));
> END;
> $function$
$$colname$$ is a string whose contents is the literal 'colname', not the
function argument named colname as you seem to want.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Doug Kyle | 2016-02-04 20:00:35 | Re: Recursive CTE in function problem |
Previous Message | Alex Magnum | 2016-02-04 19:42:49 | Cannot Create Objects |