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