DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references;
	b=GmkqlWesFqTKgZJeVsVVHPgi/uAzNVcjSYlY1KoG5KJ/7KeSvehKZpg3lm4V8MmTMG++3N9b7bjHiw7NmG3OXYRTk9lyY9hRTVOEt74Gt96YD1unHB12cpc4hJ6w8oIdhYbBNx8MUJ2hzsKh0UtAzkAgREE68oIFLdde+XA6I/o=
Message-ID: <b918cf3d04112208564c5fb783@mail.gmail.com>
Date: Mon, 22 Nov 2004 11:56:31 -0500
From: Mike Rylander <mrylander@gmail.com>
Reply-To: Mike Rylander <mrylander@gmail.com>
To: Richard Rowell <richard@bowmansystems.com>
Subject: Re: Recursive SETOF function
Cc: pgsql-sql@postgresql.org
In-Reply-To: <b918cf3d041122085437cde2f1@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
References: <1101136693.18148.66.camel@richard>
	<b918cf3d041122085437cde2f1@mail.gmail.com>

Forgot one line.  See below

On Mon, 22 Nov 2004 11:54:30 -0500, Mike Rylander <mrylander@gmail.com> wrote:
> I'm feeling sausey today, so here is my (untested) attempt to
[snip]
> > CREATE FUNCTION svp_getparentproviderids (@child_provider INTEGER)
> > RETURNS @provider_ids TABLE ( uid INTEGER )
> > AS
> > BEGIN
> >     DECLARE @cid AS INTEGER
> >     IF (SELECT count(*) FROM providers WHERE uid =@child_provider) > 0
> >     BEGIN
> >         SET @cid = @child_provider
> >         WHILE @cid IS NOT NULL
> >         BEGIN
> >             INSERT @provider_ids VALUES (@cid)
> >             SET @cid = (SELECT parent_id FROM providers WHERE uid=@cid)
> >         END
> >     END
> >     RETURN
> > END
> >
> 
> -- This TYPE will get you a named column... easier to use SRFs with a
> preexisting type.
> CREATE TYPE svp_getparentproviderids_uid_type AS ( uid INTEGER );
> 
> CREATE FUNCTION svp_getparentproviderids (INTEGER)
>    RETURNS SETOF svp_getparentproviderids_uid_type
>    AS '
> DECLARE
>   child_provider ALIAS FOR $1;
>   cid INTEGER;
> BEGIN

    SELECT * FROM providers WHERE uid =@child_provider) > 0
    IF NOT FOUND
        RETURN;
    END IF;

>     LOOP
>         cid := child_provider
>         IF cid IS NULL THEN
>           EXIT;
>         END IF;
>         RETURN NEXT cid;
>         SELECT INTO cid parent_id FROM providers WHERE uid=@cid;
>     END LOOP;
>     RETURN
> END;' LANGUAGE 'plpgsql';
> 
> Hope that helps!
> 
> > --
> 
> 
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> >       subscribe-nomail command to majordomo@postgresql.org so that your
> >       message can get through to the mailing list cleanly
> >
> 
> 
> --
> Mike Rylander
> mrylander@gmail.com
> GPLS -- PINES Development
> Database Developer
> 


-- 
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer