I'm trying to port some TSQL to PLPGSQL. The DB has a table with a
recursive foreign key that represents a tree hierarchy. I'm trying to
re-create a TSQL function that pulls out all the ancestors of a given
node in the hierarchy.

I'm rather new to PLSQL and I have several questions.

1. In TSQL, I can assign a scalar to the result of query like so:
SET @var1 = (SELECT foo FROM bar WHERE bar.uid=@var2)

How would I do this in PLSQL?

2. In TSQL the "result table" can be inserted into manually. IE:

CREATE FUNCTION foo () RETURNS @ttable TABLE( uid INTEGER) AS BEGIN
INSERT @ttable VALUES (1)
RETURN
END

Is there a way to manually insert rows into the result table in PLSQL?


What follows is my TSQL function if that helps give context.

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

--


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

I'm feeling sausey today, so here is my (untested) attempt to
translate your function. It's inline below, and you'll want to look
here http://www.postgresql.org/docs/7.4/i...e/plpgsql.html for
more information.

On Mon, 22 Nov 2004 09:18:13 -0600, Richard Rowell
<richard (AT) bowmansystems (DOT) com> wrote:
-- 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 count(*) FROM providers WHERE uid =@child_provider) > 0
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!


--
Mike Rylander
mrylander (AT) gmail (DOT) com
GPLS -- PINES Development
Database Developer

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Forgot one line. See below

On Mon, 22 Nov 2004 11:54:30 -0500, Mike Rylander <mrylander (AT) gmail (DOT) com> wrote:
SELECT * FROM providers WHERE uid =@child_provider) > 0
IF NOT FOUND
RETURN;
END IF;


--
Mike Rylander
mrylander (AT) gmail (DOT) com
GPLS -- PINES Development
Database Developer

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

I have been fiddling with what you sent. I have it working mostly, save
for I keep getting syntax errors on the "RETURN NEXT cid;" line. If I
remove this line then the function works ( but returns nothing of
course). Any ideas on why the RETURN NEXT doesn't like the variable as
a parameter?

sp_demo_505=# CREATE OR REPLACE FUNCTION svp_getparentproviderids
(INTEGER)
RETURNS SETOF svp_getparentproviderids_uid_type
AS '
DECLARE
child_provider ALIAS FOR $1;
cid INTEGER;
BEGIN
SELECT INTO cid count(*) FROM providers WHERE uid =child_provider;
IF cid = 0 THEN
RAISE EXCEPTION ''Inexistent ID --> %'', child_provider;
RETURN;
END IF;
cid := child_provider;
LOOP
EXIT WHEN cid IS NULL;
RETURN NEXT cid;
SELECT INTO cid parent_id FROM providers WHERE uid=cid;
END LOOP;
RETURN;
END;' LANGUAGE 'plpgsql';
CREATE FUNCTION
sp_demo_505=# select * from svp_getparentproviderids(21112);
ERROR: incorrect argument to RETURN NEXT at or near "cid"
CONTEXT: compile of PL/pgSQL function "svp_getparentproviderids" near
line 13



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

Sorry about that... try this:

CREATE OR REPLACE FUNCTION svp_getparentproviderids
(INTEGER)
RETURNS SETOF svp_getparentproviderids_uid_type
AS '
DECLARE
child_provider ALIAS FOR $1;
cid svp_getparentproviderids_uid_type%ROWTYPE;
tmp_cid INTEGER;
BEGIN
SELECT INTO tmp_cid count(*) FROM providers WHERE uid =child_provider;
IF tmp_cid = 0 THEN
RAISE EXCEPTION ''Inexistent ID --> %'', child_provider;
RETURN;
END IF;
cid.uid := child_provider;
LOOP
EXIT WHEN tmp_cid IS NULL;
RETURN NEXT cid;
SELECT INTO tmp_cid parent_id FROM providers WHERE uid=cid.uid;
END LOOP;
RETURN;
END;' LANGUAGE 'plpgsql';

On Mon, 22 Nov 2004 12:51:41 -0600, Richard Rowell
<richard (AT) bowmansystems (DOT) com> wrote:

--
Mike Rylander
mrylander (AT) gmail (DOT) com
GPLS -- PINES Development
Database Developer

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

I had to fiddle a bit more, but I did finally get it to work. Thanks
Mike

CREATE OR REPLACE FUNCTION svp_getparentproviderids (INTEGER)
RETURNS SETOF INTEGER
AS '
DECLARE
child_provider ALIAS FOR $1;
cid INTEGER;
BEGIN
SELECT INTO cid count(*) FROM providers WHERE uid =child_provider;
IF cid = 0 THEN
RAISE EXCEPTION ''Inexistent ID --> %'', child_provider;
RETURN;
END IF;
cid := child_provider;
LOOP
EXIT WHEN cid IS NULL;
RAISE WARNING ''LOOP: Adding (%) to results'', cid;
RETURN NEXT cid;
SELECT INTO cid parent_id FROM providers WHERE uid=cid;
END LOOP;
RETURN;
END;' LANGUAGE 'plpgsql';


On Mon, 2004-11-22 at 14:39 -0500, Mike Rylander wrote:


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Hey, hey.
Better :

SELECT blablah FROM providers WHERE uid = child_provider LIMIT 1;
IF NOT FOUND THEN exit with error ELSE do your stuff

Why scan more than 1 row when you just need existence ?
Or :

SELECT INTO cid parent_id FROM providers WHERE uid=cid;
WHILE FOUND
RETURN NEXT cid;
SELECT INTO cid parent_id FROM providers WHERE uid=cid;
END;

Not sure about the While syntax but you get the idea.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings