Обсуждение: 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
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
Thank you for the help.
-Mike
On 3/28/06, A. Kretschmer <andreas.kretschmer@schollglas.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.kretschmer@schollglas.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 Sch�nig, Sams (2002) and perhaps (hopefully) a later edition of the same.