Re: Stored Procedures?

> I read something about stored procedures in the Great Bridge User's Manual
> (Page 74 under "PG_LANGUAGE"). It is only mentioned briefly and there are no
> explanations of how it works.
>
> Can anybody let me know, how I can write a stored procedure and how to run
> it?

Postgres doesn't have stored procedures in the same way that other
databases like oracle and sybase do. But it does have stored functions,
and they can be used in almost exactly the same way.

You create a function like this:

CREATE FUNCTION get_country(text) RETURNS text AS '
DECLARE
country_name country.name%TYPE;
country_key country.key%TYPE;
country_rec RECORD;
BEGIN
IF $1 ISNULL THEN
RETURN NULL;
END IF;
country_name = initcap($1);
SELECT INTO country_rec * FROM country
WHERE name = country_name;
IF FOUND THEN
RETURN country_rec.key;
END IF;

country_key := nextval(''country_key_seq'');
INSERT INTO country
VALUES (country_key, country_name);
RETURN country_key;
END;
' LANGUAGE 'plpgsql';

(You will need to load plpgsql support into your database. See the
createlang command for details.)

And you call it with SELECT, like this:

SELECT get_country('Zimbabwe');

Or from INSERT, like this:

INSERT INTO person (name, country_key)
VALUES ('Fred', get_country('Japan'));

The only difference between a function and a procedure is that a function
returns a value. If you don't need to return a value just pick a random
small result type (like bool, or int) return NULL, and ignore the return
value.

Usually I return a value even from procedural functions though just to
indicate if things went ok or not.
--
Tod McQuillin