Re: books on pl/pgsql

Darren Ferguson

On Wed, 15 May 2002, Johnson, Shaunn wrote:

> First off, thanks to all for the tips
> for trying to do a bit of Pl/Pgsql
> programming.
>
> I really don't like to start asking
> the group questions about Pl/Pgsql without
> at least some research, is there a good
> books about Pl/Pgsql out there for
> beginners?
>
> I'm really trying to get tips and examples
> (especially examples) in the following:
>
> * defining a variable

Define the variables in the DECLARE section. Any variable that you will
use will be in this section examples

CREATE OR REPLACE FUNCTION variable_test(INTEGER,VARCHAR) RETURNS BOOLEAN
AS '
DECLARE
_id ALIAS FOR $1; // Constant cannot change its value
_name ALIAS FOR $2; // Constant value cannot change value
value INTEGER; // defining an integer. Will have default null value
BEGIN
RETURN TRUE;
END;' LANGUAGE 'plpgsql';

> * using if-then-else statements
> (i'm getting errors now ... syntax i think)

In the begin use if then else

BEGIN
IF _name = ''Darren'' THEN
do something;
ELSE
so other thing
END IF
END;'

> * using the exception (or, is it raised exception?)

RAISE NOTICE OR RAISE EXCEPTION

CREATE OR REPLACE FUNCTION test() RETURNS VARCHAR AS '
DECLARE
_name VARCHAR := ''Darren'';
BEGIN
RAISE NOTICE ''My name is %'',_name;
RETURN _name;
END;' LANGUAGE 'plpgsql';

SELECT test();

RAISE NOTICE My name is Darren
test
--------------
Darren

> * execute sql statements within the BEGIN / END block

EXECUTE sql normal for insert or update. SELECT has SELECT INTO

DECLARE
_foo VARCHAR;
BEGIN
SELECT foo INTO foo FROM bar;
UPDATE bar SET foo = ''Darren'';
INSERT INTO bar VALUES (''Test'');
END;'

>
> I'm really trying to do more work without
> having to create a bunch of functions.
> I only have the 'Postgres: Introduction and
> Concepts' book and an 'Oracle 8i: Beginners
> Guide' to help me. Both aren't very good at
> what I'm looking for.
>
> Suggestions? TIA!
>
> -X
>

These are just some of the things. The plpgsql manual in the 7.2
documentation is very good and explains a lot more of these conceptd
including EXECUTE, PERFORM, RECORDS and LOOPING.

HTH

Darren Ferguson