From: "GH" <grasshacker@over-yonder.net>
> On Tue, Jun 26, 2001 at 11:19:41AM +0530, some SMTP stream spewed forth:
> > Hi,
> > 1: What are the stored procedures?
>
> A stored procedure is a basically "compiled" series of instruction to the
> database which are, well, stored and called by an outside client or a
> trigger, or whatever. It is my understanding that stored procedures can
> be written in (generally) any language supported by the database (which
> for PostgreSQL is at least SQL, C, and maybe Python and some others).
See "Procedural Languages" in the Programmer's guide - you can write
functions in C (or anything else that could produce a C-compatible shared
library I'd have thought) or pl/pgsql, pl/tcl and pl/perl (new and still a
bit experimental AFAIK).
Note that PG functions can't return sets of records.
> > 2: How they are used in database server?
>
> Like a regular function, if I recall correctly.
Yes - you might have a products table and use a custom function to calculate
shipping costs based on weight/size for example.
The other use is when you have a problem that raw SQL is bad at. If you
represent a tree of categories as a single table with "parent" links you
need some procedural way (a loop) to repeatedly fetch parents until you
reach the root. SQL is poor at this sort of thing.
You can also write your own aggregate functions like min() and max() if you
find the need.
> > 3: How the developer can make use of those stored procedures?
>
> Er, same as [2], maybe?
Remember to "createlang" on your database before using one of the procedural
languages. Bear in mind this sort of stuff is never portable between
different databases.
> > 4: Where I can get stored procedures for PostgreSQL in its directory?
>
> I propose you write them. There may be some public libraries, but none
> that I know of. (Although, I am not someone who *would* know.)
Look in the "contrib" directory of the source distribution, read the
Programmer's Guide, and look at http://techdocs.postgresql.org especially
the cookbook and perhaps a couple of pages of my Postgresql Notes.
When you write your own functions, keep them in a separate text file and use
\i to import them from psql. Start off with a very simple function and be
careful with quoting. Error messages are not always obvious, so it helps to
add code step by step.
> It is entirely possible and even likely that I have just lied to you.
> Someone else please correct me if I have.
If you are, then we're both wrong ;-)
HTH
- Richard Huxton
