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
