Working on my first set returning function... So far the examples from http://techdocs.postgresql.org/guides/SetReturningFunctions have worked well for me...
I'd like to see what kind of performance I get from a particularly slow piece of code by replacing it with a recursive srf (right now, I do the recursion in php). So, here's my working example, I haven't bench marked it yet, but if someone would look at it and tell me if there's any improvements that can be made, I'd appreciate it. My first impression is that it's fast, because it appeared to have returned instantaneously. I really don't understand the "explain analyze" output, but I'm including it as well. I'd love to get some feedback on this (did I say that already?). Imagine this: CREATE TYPE nav_list AS (id int8, accountid varchar(12), ...snip... , parent int8, subfolders int8); subfolders is the count() of records that have their parent set to this record's id. I want to take a list of something like this: home - item 1 - item 2 - sub item 1 - item 3 and return it so that it comes out in this order home item1 item2 sub item 1 item 3 create or replace function nav_srf(varchar(12), int8) returns setof nav_list as ' DECLARE r nav_list%rowtype; depth int8; last_id int8; records RECORD; BEGIN FOR r IN SELECT * FROM navigation WHERE accountid = $1 AND parent = $2 ORDER BY dsply_order LOOP depth := r.subfolders; last_id := r.id; RETURN NEXT r; IF depth > 0 THEN FOR records IN SELECT * FROM nav_srf($1, last_id) LOOOP RETURN NEXT records; END LOOP; END IF; END LOOP; RETURN; END ' LANGUAGE 'plpgsql'; # EXPLAIN ANALYZE SELECT * FROM nav_srf('GOTDNS000000', 0); QUERY PLAN Function Scan on nav_srf (cost=0.00..12.50 rows=1000 width=134) (actual time=85.78..86.19 rows=22 loops=1) Total runtime: 86.37 msec (2 rows) I then ran it again a moment later and got: # EXPLAIN ANALYZE SELECT * FROM nav_srf('GOTDNS000000', 0); QUERY PLAN Function Scan on nav_srf (cost=0.00..12.50 rows=1000 width=134) (actual time=23.54..23.97 rows=22 loops=1) Total runtime: 24.15 msec (2 rows) BTW, this started out as a question about how to do it, but in the process of thinking my question out, the answer came to me. ;-) Matthew Nuzum www.bearfruit.org [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html