Postgresql – When to use stored procedure / user-defined function

functionspostgresqlstored-procedures

I'm confused about usage of SP (stored procedures) and UDF (user defined functions). Typically, it is also possible to write code in programs outside of the database.

Is there any general advice to decide when to use them?

Best Answer

Update

Postgres 11 finally introduces SQL procedures ("stored procedures") that can also start and end transactions:

The manual for CREATE PROCEDURE.
2ndquadrant blog entry introducing the feature.

However, commands that are not allowed in a transaction block can not (yet) be included. Important examples CREATE DATABASE or CREATE INDEX CONCURRENTLY or VACUUM.

And procedures can only return a single result row, yet, when created with INOUT parameters.

True stored procedures would not impose these restrictions. Further improvements may come with later releases.

Original answer

Before Postgres 11 there were no "stored procedures", strictly speaking. Just functions, doing almost but not quite the same. Most importantly, functions always run inside a transaction, which matters for lock management, trapping errors, or commands that cannot be run in a transaction context (see above). Related:

In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?

Processing data in SQL can have major benefits over retrieving all raw data into your application and doing calculations there:

What are the pros and cons of performing calculations in sql vs. in your application

The same principle can apply to bigger operations that can't be done in a single statement, not even when using "chained" commands in a (data-modifying) CTE:

Depending on the use case, a function can be superior in performance, and often more convenient - especially when multiple apps access the same database. You have to chose a server-side language, most commonly SQL or PL/pgSQL:

In the presence of competing concurrent transactions (multi-user environments) be brief. Only put as much into the same function as needs to be atomic. If possible, avoid long transactions with many locks. Locks are only released at the end of a transaction. Long transactions may stall concurrent access, even lead to deadlocks (which are detected and resolved by Postgres automatically, by raising an error for one or more competing transaction, which are then rolled back) ...

If you avoid those anti-patterns, server-side functions can be a great tool. For some purposes you must use functions anyway, like trigger functions.

Related Solutions

Sql-server – Disallow new UDFs that contain a cursor

CREATE TRIGGER PreventCursorUDFs
    ON DATABASE 
    FOR CREATE_FUNCTION
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @EventData XML = EVENTDATA();

    IF LOWER(@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]','NVARCHAR(MAX)'))
      LIKE N'%declare%cursor%fetch%'
    BEGIN
        RAISERROR('Yo, no cursors in functions!', 11, 1);
        ROLLBACK;
    END
END
GO

With a caveat, this will also disallow a trigger that contains a comment, like:

/* we used to do this a dumb way, using DECLARE CURSOR and FETCH */
/* now we're a little smarter and use this table-valued function */

...or if you have really inconvenient queries such as:

SELECT [declare] = [cursor] * 10 FROM dbo.[fetch];

...or even if you call your function:

CREATE FUNCTION dbo.ModeClarevoyantForCursoryFetching()
RETURNS TABLE
AS ...

EDIT

Addressing Nick's question here instead of as a comment, because it's going to get a little long-winded.

PBM is great for some stuff, but not so great at other stuff. This requirement falls under the "other stuff" category. The main problem is that the definition (e.g. OBJECT_DEFINITION()) is not exposed as a property to facets like user-defined functions. This means your condition cannot simply express something like:

@ObjectDefinition NOT LIKE '%DECLARE%CURSOR%FETCH%'

If this were the case, you could create this condition, wrap a policy around it, set it to "On prevent: change" and go to lunch. To implement this as a policy requires a little more work.

Since @ObjectDefinition is not a valid Field for this facet, we need to obtain it using ExecuteSql(). So your condition would have to be something like:

ExecuteSql('Numeric', 'SELECT x = PATINDEX(''%declare%cursor%fetch'',
  LOWER(OBJECT_DEFINITION(OBJECT_ID(@@SchemaName + ''.'' + @@ObjectName))))')

Ugly, right? When this evaluates to 0, the policy should succeed; when it is <> 0, the policy should fail. (Remember that a policy is supposed to be expressed in terms of the state you want the system to be in, not the state that you don't.)

So to start you would create a condition by opening Object Explorer, expanding Management > Policy Management, right-clicking Conditions, and selecting New Condition... Give it a name, choose the User Defined Function facet, and click on the Advanced Edit button. There you can enter the ExecuteSql() string above and click OK.

enter image description here

Change the Operator to =, enter 0 as the Value, and click OK. Now create a Policy. Right-click Policies, New Policy... Give it a name, choose the condition you just created, and then choose the Evaluation Mode:

enter image description here

Uh oh. Why aren't "On prevent" actions available? On prevent: change of course would allow you to prevent the function from being created. Because properties like ID and definition aren't available through the facet, requiring us to go through ExecuteSql(), we are subject to a restriction that prevents policies with conditions using ExecuteSql() from being automated. While this PBM blog post from 2008 suggests that this restriction has been lifted, I'm still finding it is enforced in SQL Server 2012 with Cumulative Update #1 applied (11.0.2316).

So for now, you can implement this using a policy, but you won't be able to prevent such a function from being created - you will only be able to investigate violations after the fact (by choosing either On Demand or On Schedule). Keep in mind that even if you run this policy on demand, it uses the exact same logic as the DDL trigger, so is subject to the same caveat: it may introduce false positives if you have comments or valid non-cursor queries that include the same sequence of words.

If you want the properties available to facets to be more flexible and more complete, which would allow for more control in using policies in favor of DDL triggers, please vote for and comment on these two Connect items:

http://connect.microsoft.com/SQLServer/feedback/details/552345/pbm-add-objectid-as-a-parameter-for-executesql

http://connect.microsoft.com/SQLServer/feedback/details/649944/pbm-enable-the-ability-to-pass-more-parameters-to-executesql

I don't know of an item that addresses relaxing the restriction on ExecuteSql() in general. I will look later and file one if I can't find one. I think the blog post reflects the fact that you can now run these on a schedule, but if you can run it on a schedule, why can't it work in "on prevent" mode?

Closing the loop: here is the new Connect item. Please vote and/or add comments describing your use case / business need (this is often much more valuable than just raw votes):

http://connect.microsoft.com/SQLServer/feedback/details/749317/allow-on-change-prevent-for-policies-with-executesql-conditions

Sql-server – User defined function performance disadvantages

There comes a time when you have to decide what is more important to you, maintenance of the code or the speed at which it runs? The reason it is running slow is because UDF's get processed on a row-by-row basis - SQL Server performs best using set-based operations. There is no reason why you cant keep your UDF's as there may be time where it is more practical to use them than to expand out the query.

My advice is this: If your data set is small feel free to use the UDF's; but if you are working with a large data set then take the time to write, test and optimize the query to obtain the best results - it will benefit you in the long run when you don't have users complaining their system is slow.