Ladies and Gentlemen, I'm using PostgreSQL 8.1 for a web application. 1. Is there a good comprehensive resource on plpgsql anywhere? I have "PostgreSQL, Second Edition" by Douglas and Douglas, but the PL/pgSQL chapter layout is frustrating to the point of making me tear my hair out. At the very least, I would like a complete list of parameter types, calling syntax, and return types.
2. What is the syntax for calling a plpsql function that returns a set of values? I wrote a sample function 'return_three () RETURN INTEGER' on a test database that returns the number 3, and then did SELECT * FROM informant WHERE informant.episode = return_three(). It worked as expected. Then I wrote a slightly more complicated function as follows: CREATE OR REPLACE FUNCTION get_informant_id_for_episode_three () RETURNS SETOF RECORD AS $$ DECLARE episode INTEGER; inf_row RECORD; BEGIN FOR inf_row IN SELECT informant.informant_id, informant.episode FROM informant WHERE informant_type_code = 'CONSUMER' AND informant.episode = episode LOOP RETURN NEXT inf_row; END LOOP; END $$ LANGUAGE 'plpgsql';
I try to invoke 'get_informant_id_for_episode_three()' but I just get a syntax error at the first character.
3. The software I am using has PostgreSQL as the backend for a Java web application. We discovered a big design flaw in the software and prevented future data from getting entered with bad values, but now we need to go back and fix the hundreds of table rows that were already affected. I've written JDBC apps to handle simple database work in the past, but since this fix should only need to be run once, I'm thinking it may be quicker to put together a PL/pgSQL query to take care of it.
Anyone care to guess whether I'm fooling myself if I think I can pick up enough PL/pgSQL to fix it in just a week or two?