Thread: Recursive CTE in function problem
Postgres 9.4.1, with these tables: grpl_collection.collections id | integer | name | text | parent | bigint | (An adjacency list representing a collections hierarchy) grpl_collection.collections_copy_map id | integer | collection | bigint | copy | bigint | (map a copy to the collections it belongs to) This query correctly answers if a copy is in a collection tree: select 3006377 in (select copy from grpl_collection.collections_copy_map where copy=3006377 and collection in (WITH RECURSIVE q AS ( SELECT c.id FROM grpl_collection.collections c WHERE name=$$Movies$$ UNION ALL SELECT cn.id FROM q JOIN grpl_collection.collections cn ON cn.parent = q.id ) SELECT id FROM q)); 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$ What am I missing?
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.
ugh, simple syntax screw up. Thanks David.
On 02/04/2016 02:45 PM, David G. Johnston 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.