Harpreet Dhaliwal wrote:
lately I have been looking at difference between a Stored
Proc and User Defined Functions in other RDBMS like Sql
Server / Oracle.
Nomenclature varies wildly between different Database
Management Systems. Be careful.
The SQL standard (2005) speaks of SQL-invoked routines and
devides those into SQL-invoked procedures and SQL-invoked
functions (chapter 11.50).
The difference is mainly that functions are created with
CREATE FUNCTION and have a return value, while
procedures are created with CREATE PROCEDURE and have no
return value.
Is that what you are talking about?
If not, maybe you should explain it in more detail.
Oracle indeed uses the term User-Defined Function and it
uses it in the above sense of a function (see
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions231.htm#sthref2615).
However, in postgresql, I think Stored Procs are wrapped
around in User Defined functions, if I am not wrong.
In PostgreSQL, there are only functions (in the sense of
the SQL standard).
This will probably answer most of your questions.
The following is the list of main differences b/w a Stored
Proc and a UDF in general.
Did you compile that list by examining the implementation
details of MS SQL Server and Oracle?
Can anyone please comment on how a
postgresql UDF would behave for each of these difference
mentioned below ?
1. Stored Procedures are parsed and compiled and stored in
compiled format in the database. We can also say that
Stored Procedures are stored as pseudo code in the
database i.e. compiled form. On the other hand, User Defined
Functions are parsed, and compiled at runtime.
PostgreSQL functions are not compiled, but the execution plans
that are generated for SQL statements in the function are
retained until the end of the session.
For Oracle, your statement is not true, because both
functions and procedures (when written in PL/SQL) are
compiled upon first use. The compiled version is retained
until something renders it invalid or the database server
is stopped.
2. A User Defined Function must return a value where as a
Stored Procedure doesn't need to (they definitely can,
if required).
If you are talking about output parameters, you are wrong
because the SQL standard allows them for both functions
and procedures.
If you are only talking about return values, you are wrong
because procedures do not have any.
Oracle sticks with the standard here.
In PostgreSQL functions, you can have composite return values,
so you can work around the limitation that a function has only
one return value.
3. A User Defined Function can be used with any Sql
statement. For example, [...]
On the other hand, Stored Procedures can't be called inside a
Sql statement.
Strange.
The SQL standard has the CALL statement to invoke an
SQL-invoked routine (see chapter 15.1 of SQL-Foundation).
Oracle, for one, implements the CALL statement.
4. Operationally, when an error is encountered, the function
stops, while an error is ignored in a Stored Procedure and
proceeds to the next statement in the code (provided
one has included error handling support).
That is wrong, at least in Oracle.
The standard will probably only say something about
PSM functions and routines, but I can't be bothered to look
it up.
5. Functions return values of the same type, Stored
Procedures return multiple type values.
Procedures have no return values.
If you mean output parameters, you are wrong because
they are typed.
6. Stored Procedures support deferred name resolution. To
explain this, lets say we have a
stored procedure in which we use named tables tbl x and tbl y
but these tables actually
don't exist in the database at the time of this stored
procedure creation. Creating such
a stored procedure doesn't throw any error. However, at
runtime, it would definitely
throw error it tables tbl x and tbl y are still not there in
the database. On the other
hand, User Defined Functions don't support such deferred name
resolution.
In PostgreSQL, the existence of a table in an SQL statement
inside a function will not be checked at function definition
time.
Additionally, you have the parameter check_function_bodies
(default on) that determines whether syntax checking of function
bodies will be performed at definition time.
I hope that answers some of your questions.
I have the feeling that the amount of contradictions indicated that
there is a misunderstanding, and you were talking about something
else than I was.
Yours,
Laurenz Albe
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
