Re: books on pl/pgsql - Mailing list pgsql-general

From Darren Ferguson
Subject Re: books on pl/pgsql
Date
Msg-id Pine.LNX.4.10.10205152208560.17633-100000@thread.crystalballinc.com
Whole thread Raw
In response to books on pl/pgsql  ("Johnson, Shaunn" <SJohnson6@bcbsm.com>)
List pgsql-general
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


pgsql-general by date:

Previous
From: "Dorward Villaruz"
Date:
Subject: Re: some questions
Next
From: "patrick k ."
Date:
Subject: How to include PostgreSQL Copyright notice