agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Tod McQuillin <devin@spamcop.net>
To: Chris Ruprecht <chrup999@yahoo.com>
Cc: psql <pgsql-sql@postgresql.org>
Subject: Re: Stored Procedures?
Date: Thu, 24 May 2001 08:39:50 -0500 (CDT)
Message-ID: <Pine.GSO.4.33.0105240831060.4117-100000@sysadmin> (raw)
In-Reply-To: <001101c0e44a$3f362600$5dd26383@corp.compucom.com>

> 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

view thread (23+ messages)  latest in thread

Message-ID: <Pine.GSO.4.33.0105240831060.4117-100000@sysadmin>
Permalink:  ../Pine.GSO.4.33.0105240831060.4117-100000@sysadmin/
Also on:    postgresql.org/message-id/Pine.GSO.4.33.0105240831060.4117-100000@sysadmin

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: devin@spamcop.net, chrup999@yahoo.com
  Subject: Re: Stored Procedures?
  In-Reply-To: <Pine.GSO.4.33.0105240831060.4117-100000@sysadmin>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox