From: | Doug Kyle <dkyle(at)grpl(dot)org> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Recursive CTE in function problem |
Date: | 2016-02-04 20:00:35 |
Message-ID: | 56B3ADE3.40709@grpl.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
ugh, simple syntax screw up. Thanks David.
On 02/04/2016 02:45 PM, David G. Johnston wrote:
> On Thu, Feb 4, 2016 at 12:37 PM, Doug Kyle <dkyle(at)grpl(dot)org
> <mailto: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 <http://c.id>
> FROM grpl_collection.collections c
> WHERE name=$$colname$$
> <<<------------
>
> UNION
> SELECT cn.id <http://cn.id>
> FROM q
> JOIN grpl_collection.collections cn
> ON cn.parent = q.id <http://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 | Adrian Klaver | 2016-02-04 20:18:41 | Re: Cannot Create Objects |
Previous Message | David G. Johnston | 2016-02-04 19:45:48 | Re: Recursive CTE in function problem |