Hızlı yanıt: kod örneği
SQL functions... are the better choice:There are other ways:
- For simple scalar queries. Not much to plan, better save any overhead.
- For single (or very few) calls per session. Nothing to gain from plan caching via prepared statements that PL/pgSQL has to offer. See below.
- If they are typically called in the context of bigger queries and are simple enough to be inlined.
- For lack of experience with any procedural language like PL/pgSQL. Many know SQL well and that's about all you need for SQL functions. Few can say the same about PL/pgSQL. (Though it's rather simple.)
- A bit shorter code. No block overhead.
- When you need any procedural elements or variables that are not available in SQL functions, obviously.
- For any kind of dynamic SQL, where you build and statements dynamically. Special care is needed to avoid SQL injection. More details:
EXECUTE- Postgres functions vs prepared queries
- When you have computations that can be reused in several places and a CTE can't be stretched for the purpose. In an SQL function you don't have variables and would be forced to compute repeatedly or write to a table. This related answer on dba.SE has side-by-side code examples for solving the same problem using an SQL function / a plpgsql function / a query with CTEs:
- How to pass a parameter into a function
- When a function cannot be inlined and is called repeatedly. Unlike with SQL functions, query plans can be cached for all SQL statements inside a PL/pgSQL functions; they are treated like prepared statements, the plan is cached for repeated calls within the same session (if Postgres expects the cached (generic) plan to perform better than re-planning every time. That's the reason why PL/pgSQL functions are typically faster after the first couple of calls in such cases.Here is a thread on pgsql-performance discussing some of these items:
- Re: pl/pgsql functions outperforming sql ones?
- When you need to trap errors.
- For trigger functions.
- When including DDL statements changing objects or altering system catalogs in any way relevant to subsequent commands - because all statements in SQL functions are parsed at once while PL/pgSQL functions plan and execute each statement sequentially (like a prepared statement). See:
- Why can PL/pgSQL functions have side effect, while SQL functions can't?
- PostgreSQL Stored Procedure Performance
CREATE FUNCTION f2(istr varchar) RETURNS text AS$func$BEGIN RETURN 'hello! '; -- defaults to type text anywayEND$func$ LANGUAGE plpgsql;- Can I make a plpgsql function return an integer without using a variable?
- The manual on "Returning From a Function"