Re: Recursive CTE in function problem

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.
>
>