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?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Feb 4, 2016 at 12:37 PM, Doug Kyle <dkyle@grpl.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.
ugh, simple syntax screw up. Thanks David.
Show quoted text
On 02/04/2016 02:45 PM, David G. Johnston wrote:
On Thu, Feb 4, 2016 at 12:37 PM, Doug Kyle <dkyle@grpl.org
<mailto:dkyle@grpl.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.