Difference between language sql and language plpgsql in PostgreSQL functions

by

SQL functions

… are the better choice:

  • 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.

PL/pgSQL functions

… are the better choice:

  • 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 EXECUTE statements dynamically. Special care is needed to avoid SQL injection. More details:

  • 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:

Assignments are somewhat more expensive than in other procedural languages. Adapt a programming style that doesn’t use more assignments than necessary.

Also consider:

To actually return from a PL/pgSQL function, you could write:

CREATE FUNCTION f2(istr varchar)
  RETURNS text AS
$func$
BEGIN
   RETURN 'hello! ';  -- defaults to type text anyway
END
$func$ LANGUAGE plpgsql;

There are other ways:

More Related Contents:

  1. Pass multiple values in single parameter
  2. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  3. Return setof record (virtual table) from function
  4. Functions with variable number of input parameters
  5. Refactor a PL/pgSQL function to return the output of various SELECT queries
  6. Is SELECT or INSERT in a function prone to race conditions?
  7. How to return result of a SELECT inside a function in PostgreSQL?
  8. GROUP BY and aggregate sequential numeric values
  9. PL/pgSQL checking if a row exists
  10. PostgreSQL parameterized Order By / Limit in table function
  11. PostgreSQL IF statement
  12. Call a set-returning function with an array argument multiple times
  13. What is the difference between function and procedure in PL/SQL?
  14. Record returned from function has columns concatenated
  15. Postgres Dynamic Query Function
  16. Window Functions or Common Table Expressions: count previous rows within range
  17. DROP FUNCTION without knowing the number/type of parameters?
  18. Solution to “cannot perform a DML operation inside a query”?
  19. Postgres function returning table not returning data in columns
  20. Return multiple columns and rows from a function PostgreSQL instead of record
  21. COPY with dynamic file name
  22. pseudo_encrypt() function in plpgsql that takes bigint
  23. Functions vs Stored Procedures
  24. How to perform a select query in a DO block?
  25. Function with SQL query has no destination for result data
  26. Postgres trigger after insert accessing NEW
  27. SQL Server Insert if not exists
  28. Humanized or natural number sorting of mixed word-and-number strings
  29. postgres column “X” does not exist
  30. SQL: Select dynamic column name based on variable

Leave a Comment