Thread: Stored Procs Vs User Defined Functions vis-a-vis UDF's in Postgresql

Hi,
lately I have been looking at difference between a Stored Proc and User Defined Functions in other RDBMS like Sql Server / Oracle.
However, in postgresql, I think Stored Procs are wrapped around in User Defined functions, if I am not wrong.
The following is the list of main differences b/w a Stored Proc and a UDF in general. 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.


2. A User Defined Function must return a value where as a Stored Procedure doesn't
need to (they definitely can, if required).


3. A User Defined Function can be used with any Sql statement. For example, we have a
function 'FuncSal(int)' that returns the salary of a person. This function can be used
in a Sql statement as follows:-
. SELECT * FROM tbl sal WHERE salary = FuncSal(x)
Here internally, a call would be made to User Defined Function 'FuncSal' with any
integer x, as desired, and compared with the 'salary' field of database Table tbl sal.
We can have Data Manipulation Language (DML) statements like insert, update, delete
in a function. However, we can't call such a function (having insert, update, delete)
in a Sql query. For example, if we have a function (FuncUpdate(int)) that updates a
table, then we can't call that function from a Sql query.
. SELECT FuncUpdate(field) FROM sometable; will throw error.
On the other hand, Stored Procedures can't be called inside a Sql 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).


5. Functions return values of the same type, Stored Procedures return multiple type
values.


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.


Thanks in advance,

~Harpreet

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