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