Re: plpgsql questions

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?

I appreciate the help, thank you.
-Mike

am 28.03.2006, um 11:02:28 -0500 mailte Michael Swierczek folgendes:
> 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.

Our documentation? http://www.postgresql.org/docs/8.1/interactive/

> [ SRF function ]
>
> I try to invoke 'get_informant_id_for_episode_three()' but I just get a
> syntax error at the first character.

select * from srf_function();

http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS

HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

I'm very sorry for wasting your time. I got impatient and, like a fool,
gave up on both the website documentation and the book documentation about
two paragraphs before finding the required bit of info (select * from
srf_function()).

Thank you for the help.

-Mike

On 3/28/06, A. Kretschmer <andreas(dot)kretschmer(at)schollglas(dot)com> wrote:
>
> am 28.03.2006, um 11:02:28 -0500 mailte Michael Swierczek folgendes:
> > 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.
>
> Our documentation? http://www.postgresql.org/docs/8.1/interactive/
>
> > [ SRF function ]
> >
> > I try to invoke 'get_informant_id_for_episode_three()' but I just get a
> > syntax error at the first character.
>
> select * from srf_function();
>
>
> http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS
>
>
> HTH, Andreas
> --
> Andreas Kretschmer (Kontakt: siehe Header)
> Heynitz: 035242/47215, D1: 0160/7141639
> GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
> === Schollglas Unternehmensgruppe ===
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

> On 3/28/06, A. Kretschmer <andreas(dot)kretschmer(at)schollglas(dot)com> wrote:

>> Our documentation? http://www.postgresql.org/docs/8.1/interactive/

As another novice, I would like to congratulate the authors. It is
huge! And thorough. And well-written.

There is also "PostgreSQL Developer's Handbook" by Geschwinde and
Schnig, Sams (2002) and perhaps (hopefully) a later edition of the
same.