[SQL] PL/PGSQL Cook Book
I've just spent the last day or two trying to get to grips with plpgsql and can't believe how abysmal the documetentation and examples are. I've been trawling through the mailist lists and I notice there was talk back in 1999 abouta PLPGSQL Cook Book - did anything come of this? If no one is maintaining something like this and people think its a good idea I think we should have another crack at it. I'd be happy to maintain something like this and put it up on the web, although I'm only a newbie and would rely upon user contribution. Here are some possible sections to help get people thinking. Even if you don't know the answer send me the questions and I'll add them to the list. How can I create Tree structures? Are recursive functions supported? Whats are the advanteds disadvantes of the different scipting langues PL/PGSQL, PL/Perl, PL/Tcl? How do variable scopes work in PL/PGSQL? Can I output variables from a function to the command line for debugging purposes? How to debug PL/PGSQL? Various examples for each of the statements Anyway lets discuss this, a lot could be done just from piecing together relavent tips from this mailing list. ie there are some good posts on tree structures, which if I'm willing to piece together if people think this project is worth while. Regards Mark
[SQL] Recusrive Functions in 7.0.3
If I remove the line calling PERFORM it works without problems moving the children node for the passed integer into test and then exiting. I can then repeat the process going through the output to test iteratively and do not have any problems. However if I put the PERFORM line back in to create a recursive function it just goes on forever, and I only have 6 nodes. CREATE FUNCTION get_children (integer) RETURNS integer AS ' DECLARE pnode_parent ALIAS FOR $1; rec RECORD; BEGIN FOR rec IN SELECT * FROM tree_adjacency_matrix WHERE node_parent = pnode_parent LOOP INSERT INTO test (node1, node2) VALUES(rec.node_child, rec.node_parent); PERFORM get_children(rec.node_child); END LOOP; RETURN 0; END; 'LANGUAGE 'plpgsql' Mark On Saturday 10 February 2001 03:29, Tom Lane wrote: > <[EMAIL PROTECTED]> writes: > > Are recursive functions allowed in in 7.0.3 > > Sure. > > play=> create function myfactorial(int) returns int as ' > play'> begin > play'> if $1 > 1 then > play'> return $1 * myfactorial($1 - 1); > play'> end if; > play'> return $1; > play'> end;' language 'plpgsql'; > CREATE > play=> select myfactorial(1); > myfactorial > - >1 > (1 row) > > play=> select myfactorial(10); > myfactorial > - > 3628800 > (1 row) > > play=> > > I get a stack overflow crash at about myfactorial(7500), but that seems > like a sufficient level of recursion depth for normal purposes ... > > > as I seem to be unable to > > get them to work in plpgsql, > > Are you sure you aren't asking for infinite recursion, eg by invoking > the same function with the same argument? > > regards, tom lane
[SQL] Re: Recusrive Functions in 7.0.3
Ahh I found what I was doing wrong, there was a rogue value being returned causing to infinite loop. Its fixed now. Creating that script you recommended set my thinking process straight. many thanks Mark On Saturday 10 February 2001 08:42, Tom Lane wrote: > mark proctor <[EMAIL PROTECTED]> writes: > > However if I put the PERFORM line back in to create a recursive > > function it just goes on forever, and I only have 6 nodes. > > Hm. There may be a bug here, or maybe you're still confused... but I'm > not eager to reverse-engineer your table declarations and data from this > sketch. Would you mind providing a complete example, ie a SQL script to > reproduce the problem starting from an empty database? > > regards, tom lane
Re: [SQL] What's wrong with this function
the select query returns the first row to rec. You can then access its values with: rec.field_name at END LOOP it jumps back to FOR checks to see if there any more rows and if so moves to the next row and repeats the loop. It also looks like your missing a LOOP keyword at the end of the FOR line. Here is an example that works. CREATE FUNCTION get_children (integer) RETURNS integer AS ' DECLARE pnode_parent ALIAS FOR $1; rec RECORD; BEGIN FOR rec IN SELECT * FROM tree_adjacency_matrix WHERE node_parent = pnode_parent LOOP INSERT INTO test (node1, node2) VALUES(stm.node_child, .rec.node_parent); END LOOP; RETURN 0; END; 'LANGUAGE 'plpgsql' Mark On Saturday 10 February 2001 20:23, Najm Hashmi wrote: > Jie Liang wrote: > > I just know you can use implict cursor inside the plpgsql > > e.g > > declare > > result text; > tcount int4; > > > rec record; > > begin > > FOR rec IN select_clause LOOP > > statements > > END LOOP; > > end; > > Thank you Jie for your help. I am bit confused about how it works. I want > for each row , obtained by select statment, get certain values and then do > some calculations and out put that resulst eg > for rec IN select title, dcount from songs where artist='xyz' > tcount:= tcount+rec.dcount; > END LOOP; > return tcount; > would this work ? > Thanks again for your help. > Regards, Najm
