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
