Stored Procedures and Functions

On Saturday 2. June 2007 16:47, Harpreet Dhaliwal wrote:

Hi,

Is it true that postgres doesn't have a notion of Stored Procedures
and functions is what it has instead?
RDBMS like Sql Server supports both stored procedures and functions.
So I was wondering what is the difference between a Stored Procedure
and a function.

Pascal has functions and procedures. C has only functions. That doesn't
say anything about the relative usability of each language. Those are
just names.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

Hello

Is it true that postgres doesn't have a notion of Stored Procedures and
functions is what it has instead?
RDBMS like Sql Server supports both stored procedures and functions.
So I was wondering what is the difference between a Stored Procedure and a
function.

It's true. PostgreSQL knows only functions. Difference between
procedures and function are in calling context a possibilities, and
depends on database system. Functions are called from SELECT
statements, procedures are called via statement CALL. On some systems
functions has some limits (it's not true for PostgreSQL). On some
systems procedures allow transaction con troll, returning
multirecordset (unbind selects), and more.

PostgreSQL implementation is more practical than elegant. PostgreSQL
functions hasn't classic limits, but some design points are little bit
dirty. When you start to play with OUT variables, you will see.

For beginner, difference between function and procedure is less than
small in PostgreSQL. There are two families of stored procedures:
Oracle and Microsoft. Look to their documentation.

Regards
Pavel Stehule

MySQL supports procedures and functions.

Functions can return results but cannot update the database.
Procedures can update the database but cannot return results.

However :
- a function can call a procedure that updates the database !
- a procedure can return result through OUT parameters !

It's a friggin mess. In pgsql, if you want, a STABLE or IMMUTABLE
procedure is a function since it is repeatable : it will always return the
same results with the same parameters, and has no side-effects. This is
the definition of a function.

It is better not to draw useless lines in the ground with huge "don't
walk over this line" stickers. People will always find a way around.
Better offer features that users need.

Harpreet Dhaliwal wrote:

Is it true that postgres doesn't have a notion of Stored
Procedures and functions is what it has instead?
RDBMS like Sql Server supports both stored procedures and functions.
So I was wondering what is the difference between a Stored
Procedure and a function.

I think that your questions have not been answered yet.

Yes, it is true, PostgreSQL doesn't have procedures, only functions.

The difference between a function and a procedure is that the former
has a return value, while the latter does not. Procdures can hand back
results via output parameters.

The lack of procedures in PostgreSQL is mitigated by the fact that you
can achieve everything you need with a function:

- If you don't need to return results at all, you define a function
with return type "void" (which means that nothing is returned).

- If you need to return more than one result, you can define a
function with a composite return type (or equivalently with
what PostgreSQL calls "output parameters").

Yours,
Laurenz Albe