Recursive CTE in function problem
От | Doug Kyle |
---|---|
Тема | Recursive CTE in function problem |
Дата | |
Msg-id | 56B3A85C.2000102@grpl.org обсуждение исходный текст |
Ответы |
Re: Recursive CTE in function problem
|
Список | pgsql-general |
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?
В списке pgsql-general по дате отправления: