[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
Are recursive functions allowed in in 7.0.3 as I seem to be unable to get them to work in plpgsql, it just freezes and I have to do ctrl-alt-c. ie calling the same function name from within some form of loop, possible to 4 or 5 levels? Also is it possible to output any debugging info, all I really want to be able to do is print out a variable to the screen. Final quesion for pure speed on functions does PL/PGSQL have special compilation options or would I get better performace for PL/C? Regards Mark
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 18:04, Najm Hashmi wrote: > Hi all, > Here is a plpgsql function: > flipr'#create function test_cur() returns text as' > flipr'# declare > flipr'# mycur cursor for select title from songs where song_id=10; > flipr'# usrrecord; > flipr'# resultstext; > flipr'# begin > flipr'# open mycur; > flipr'# fetch next from mycur into usr; > flipr'# close mycur; > flipr'# results:= usr.title; > flipr'# > flipr'# end; > flipr'# ' language 'plpgsql'; > CREATE > flipr=# select test_cur() as Title; > NOTICE: plpgsql: ERROR during compile of test_cur near line 2 > ERROR: parse error at or near "cursor" > > What I am doing wrong? > Thanks in advance for your help. > Regards, Najm
