Thread: Stored Procedures?

Stored Procedures?

From
"Chris Ruprecht"
Date:
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?

Best regards,
Chris



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com



Re: Stored Procedures?

From
Tod McQuillin
Date:
> 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);
RETURNcountry_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



Re: Stored Procedures?

From
reinoud@xs4all.nl (Reinoud van Leeuwen)
Date:
On Thu, 24 May 2001 13:45:46 +0000 (UTC), devin@spamcop.net (Tod
McQuillin) wrote:

>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.

In Sybase I am used to the fact that stored procedures can return a
result set to the client. Something like;

create procedure sp_example (@param int) as
begin select bla  from tablename  where somecolumn = @param
end

(of course in pratice stored procedures get a lot more complex than
this :-)

Is something like this posstible in PostgreSQL?

-- 
__________________________________________________
"Nothing is as subjective as reality"
Reinoud van Leeuwen       reinoud@xs4all.nl
http://www.xs4all.nl/~reinoud
__________________________________________________