From: Mike Rylander <mrylander@gmail.com>
To: Richard Rowell <richard@bowmansystems.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Recursive SETOF function
Date: Mon, 22 Nov 2004 11:56:31 -0500
Message-ID: <b918cf3d04112208564c5fb783@mail.gmail.com> (raw)
In-Reply-To: <b918cf3d041122085437cde2f1@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mrylander@gmail.com, richard@bowmansystems.com
Subject: Re: Recursive SETOF function
In-Reply-To: <b918cf3d04112208564c5fb783@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox