Re: Recursive CTE in function problem

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.