plpgsql questions

From: "Michael Swierczek" <mike(dot)swierczek(at)gmail(dot)com>
To: "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: plpgsql questions
Date: 2006-03-28 16:02:28
Message-ID: 68b5b5880603280802t1dc993d5p781d921fdb7afda9@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Shoaib Mir 2006-03-28 16:34:41 Re: Change database encoding
Previous Message Tom Lane 2006-03-28 15:51:26 Re: LIMIT clause and long timings