postgres stored procedure problem
- by easyrider
Hi all,
Ich have a problem in postgres function:
CREATE OR REPLACE FUNCTION getVar(id bigint)
RETURNS TABLE (repoid bigint, suf VARCHAR, nam VARCHAR)
AS $$
declare rec record;
BEGIN
FOR rec IN
(WITH RECURSIVE children(repoobjectid,variant_of_object_fk, suffix, variantname) AS (
SELECT repoobjectid, variant_of_object_fk, '' as suffix,variantname
FROM b2m.repoobject_tab
WHERE repoobjectid = id
UNION ALL
SELECT repo.repoobjectid, repo.variant_of_object_fk, suffix || '..' , repo.variantname
FROM b2m.repoobject_tab repo, children
WHERE children.repoobjectid = repo.variant_of_object_fk)
SELECT repoobjectid,suffix,variantname FROM children)
LOOP
RETURN next;
END LOOP;
RETURN;
END;
It can be compiled, but if y try to call it
select * from getVar(18)
I got 8 empty rows with 3 columns.
If i execute the following part of procedure with hard-coded id parameter:
WITH RECURSIVE children(repoobjectid,variant_of_object_fk, suffix, variantname) AS (
SELECT repoobjectid, variant_of_object_fk, '' as suffix,variantname
FROM b2m.repoobject_tab
WHERE repoobjectid = 18
UNION ALL
SELECT repo.repoobjectid, repo.variant_of_object_fk, suffix || '..' , repo.variantname
FROM b2m.repoobject_tab repo, children
WHERE children.repoobjectid = repo.variant_of_object_fk)
SELECT repoobjectid,suffix,variantname FROM children
I got exactly, what i need 8 rows with data:
repoobjectid suffix variantname
18
19 .. for IPhone
22 .. for Nokia
23 .... OS 1.0
and so on.
What is going wrong ? Please help.
Thanx in advance
