Thread: books on pl/pgsql

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
* using if-then-else statements
  (i'm getting errors now ... syntax i think)
* using the exception (or, is it raised exception?)
* execute sql statements within the BEGIN / END block

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

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

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Johnson, Shaunn
Sent: Wednesday, May 15, 2002 4:02 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] books on pl/pgsql

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
* using if-then-else statements
  (i'm getting errors now ... syntax i think)
* using the exception (or, is it raised exception?)
* execute sql statements within the BEGIN / END block

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

As a rank beginner, I found the docs very hard to
follow (admittedly, they have been improved a lot
lately).  I read an introductory text on Oracle
PL/SQL, and suddenly the docs became clear.  That
brief, high level introduction gave me the conceptual
framework on which to hang the details contained in
the docs.  You might find this approach useful too.

--- Joel Burton <joel@joelburton.com> wrote:
> books on pl/pgsqlThe online documentation at
> www.postgresql.org has a
> section in the Programmer's Manual on plpgsql. Also,
> see the material at
> techdocs.postgresql.org.
>
> Joel BURTON | joel@joelburton.com | joelburton.com |
> aim: wjoelburton
> Knowledge Management & Technology Consultant
>
>   -----Original Message-----
>   From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf
> Of Johnson, Shaunn
>   Sent: Wednesday, May 15, 2002 4:02 PM
>   To: pgsql-general@postgresql.org
>   Subject: [GENERAL] books on pl/pgsql
>
>
>   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
>   * using if-then-else statements
>     (i'm getting errors now ... syntax i think)
>   * using the exception (or, is it raised
> exception?)
>   * execute sql statements within the BEGIN / END
> block
>
>   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
>
>


__________________________________________________
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com