pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql recursion
5+ messages / 5 participants
[nested] [flat]
* plpgsql recursion
@ 2003-05-20 16:53 Stefano Vita Finzi <stefano.vita@pronesis.it>
0 siblings, 2 replies; 5+ messages in thread
From: Stefano Vita Finzi @ 2003-05-20 16:53 UTC (permalink / raw)
To: pgsql-sql
Greetings!
I have a table like:
node parent
1 2
2 3
3 4
Since i traverse this table with a recursive function, i want to avoid
infinite recursion loop. I have wrote a function to check that a new record
does not create a circular dependency. The function i wrote is as follow:
CREATE OR REPLACE FUNCTION dba_test(INTEGER,INTEGER) RETURNS TEXT AS '
DECLARE
traversing ALIAS FOR $1;
testing ALIAS FOR $2;
t_rec RECORD;
BEGIN
FOR t_rec IN SELECT node,parent FROM dba_test WHERE parent = traversing
LOOP
IF t_rec.node = testing THEN
RETURN ''Circular'';
ELSE
PERFORM dba_test(t_rec.node,testing);
END IF;
END LOOP;
RETURN ''ok'' || testing::text;
END;
' LANGUAGE 'plpgsql';
I would use this function BEFORE inserting the new row. But if i try SELECT
dba_test(4,1); i don't have the result i expect. Can i you give me an hint
where am i wrong?
Thank you!
Stefano Vita Finzi
kluge@despammed.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* plpgsql recursion
@ 2003-05-20 17:01 Kluge <kluge@despammed.com>
0 siblings, 1 reply; 5+ messages in thread
From: Kluge @ 2003-05-20 17:01 UTC (permalink / raw)
To: pgsql-sql
Greetings!
I have a table like:
node parent
1 2
2 3
3 4
Since i traverse this table with a recursive function, i want to avoid
infinite recursion loop. I have wrote a function to check that a new record
does not create a circular dependency. The function i wrote is as follow:
CREATE OR REPLACE FUNCTION dba_test(INTEGER,INTEGER) RETURNS TEXT AS '
DECLARE
traversing ALIAS FOR $1;
testing ALIAS FOR $2;
t_rec RECORD;
BEGIN
FOR t_rec IN SELECT node,parent FROM dba_test WHERE parent = traversing
LOOP
IF t_rec.node = testing THEN
RETURN ''Circular'';
ELSE
PERFORM dba_test(t_rec.node,testing);
END IF;
END LOOP;
RETURN ''ok'' || testing::text;
END;
' LANGUAGE 'plpgsql';
I would use this function BEFORE inserting the new row. But if i try SELECT
dba_test(4,1); i don't have the result i expect. Can i you give me an hint
where am i wrong?
Thank you!
Stefano Vita Finzi
kluge@despammed.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: plpgsql recursion
@ 2003-05-20 17:47 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Kluge <kluge@despammed.com>
0 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2003-05-20 17:47 UTC (permalink / raw)
To: Kluge <kluge@despammed.com>; +Cc: pgsql-sql
On Tue, 20 May 2003, Kluge wrote:
> Greetings!
> I have a table like:
>
> node parent
> 1 2
> 2 3
> 3 4
>
> Since i traverse this table with a recursive function, i want to avoid
> infinite recursion loop. I have wrote a function to check that a new record
> does not create a circular dependency. The function i wrote is as follow:
>
> CREATE OR REPLACE FUNCTION dba_test(INTEGER,INTEGER) RETURNS TEXT AS '
> DECLARE
> traversing ALIAS FOR $1;
> testing ALIAS FOR $2;
> t_rec RECORD;
> BEGIN
> FOR t_rec IN SELECT node,parent FROM dba_test WHERE parent = traversing
> LOOP
> IF t_rec.node = testing THEN
> RETURN ''Circular'';
> ELSE
> PERFORM dba_test(t_rec.node,testing);
Here you're calling dba_test recursively and ignoring its result and going
on. I don't think that'll do what you want. If parent is unique, I think
you can just RETURN dba_test(t_rec.node, testing). If it's not, you'll
have to do some additional work to return the correct result.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: plpgsql recursion
@ 2003-05-23 01:59 Rod Taylor <rbt@rbt.ca>
parent: Stefano Vita Finzi <stefano.vita@pronesis.it>
1 sibling, 0 replies; 5+ messages in thread
From: Rod Taylor @ 2003-05-23 01:59 UTC (permalink / raw)
To: Stefano Vita Finzi <stefano.vita@pronesis.it>; +Cc: pgsql-sql
> IF t_rec.node = testing THEN
> RETURN ''Circular'';
> ELSE
> PERFORM dba_test(t_rec.node,testing);
> END IF;
> I would use this function BEFORE inserting the new row. But if i try SELECT
> dba_test(4,1); i don't have the result i expect. Can i you give me an hint
> where am i wrong?
It's probably working as it's written :) Once it finds 'Curcular' and
returns it to the recursed call of dba_test, you immediately throw away
the return value.
PERFORM should probably be replaced with:
ELSE
-- ret is a variable
SELECT dba_test(t_rec.node, testing) INTO ret;
IF ret = ''Circular'' THEN
RETURN ret;
END IF;
END IF;
More to the point, if you're attempting to prevent circular items, and
this function will be used in the form of a trigger, then simply RAISE
EXCEPTION. This way you can ignore having to deal with returned values.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: plpgsql recursion
@ 2003-05-28 09:03 Luis Sousa <llsousa@ualg.pt>
parent: Stefano Vita Finzi <stefano.vita@pronesis.it>
1 sibling, 0 replies; 5+ messages in thread
From: Luis Sousa @ 2003-05-28 09:03 UTC (permalink / raw)
To: Stefano Vita Finzi <stefano.vita@pronesis.it>; +Cc: pgsql-sql
Can you post the recursion function that you use on this case to get out
the information from the table ?
Thanks,
Luis Sousa
Stefano Vita Finzi wrote:
>Greetings!
>I have a table like:
>
>node parent
> 1 2
> 2 3
> 3 4
>
>Since i traverse this table with a recursive function, i want to avoid
>infinite recursion loop. I have wrote a function to check that a new record
>does not create a circular dependency. The function i wrote is as follow:
>
>CREATE OR REPLACE FUNCTION dba_test(INTEGER,INTEGER) RETURNS TEXT AS '
> DECLARE
> traversing ALIAS FOR $1;
> testing ALIAS FOR $2;
> t_rec RECORD;
> BEGIN
> FOR t_rec IN SELECT node,parent FROM dba_test WHERE parent = traversing
>LOOP
> IF t_rec.node = testing THEN
> RETURN ''Circular'';
> ELSE
> PERFORM dba_test(t_rec.node,testing);
> END IF;
> END LOOP;
> RETURN ''ok'' || testing::text;
> END;
>' LANGUAGE 'plpgsql';
>
>I would use this function BEFORE inserting the new row. But if i try SELECT
>dba_test(4,1); i don't have the result i expect. Can i you give me an hint
>where am i wrong?
>
>Thank you!
>
>Stefano Vita Finzi
>kluge@despammed.com
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-05-28 09:03 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-20 16:53 plpgsql recursion Stefano Vita Finzi <stefano.vita@pronesis.it>
2003-05-23 01:59 ` Rod Taylor <rbt@rbt.ca>
2003-05-28 09:03 ` Luis Sousa <llsousa@ualg.pt>
2003-05-20 17:01 plpgsql recursion Kluge <kluge@despammed.com>
2003-05-20 17:47 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
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