Difference Between Language SQL and Language Plpgsql in Postgresql Functions

Difference between language sql and language plpgsql in PostgreSQL functions

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:

  • 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

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

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

Also consider:

• PostgreSQL Stored Procedure Performance

---

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:

• Can I make a plpgsql function return an integer without using a variable?
• The manual on "Returning From a Function"

Are PL/pgSQL and SQL in PostgreSQL both at the same level as SQL/PSM standard, instead of as SQL standard only?

To clear up the terminology:

SQL is a query language that is used to select, update, delete or create data in a relational database. It has no procedural elements like loops (FOR, WHILE) or conditional statements (IF, ELSE) or variables or cursors.

CREATE FUNCTION is indeed a "SQL statement" but is is merely a "wrapper" to specify a block of code that is executed by something different than the SQL query "engine". Postgres (unlike other DBMS) supports multiple "runtime engines" that can execute the block of code that was passed to the "CREATE FUNCTION" statement - one artifact of that is that the code is actually a string so CREATE FUNCTION only sees a string, nothing else.

Because SQL has no procedural elements, you can't mix procedural code and SQL code. If you want to run procedural code you need to tell the server that you are switching "engines" somehow. This is done through the (SQL) DO command which again takes a string that it doesn't know what to do with, sends it to the server and says "here is a piece of code where the user claimed that the engine 'xyz' can execute" - xyz is either PL/pgSQL, Python, Perl or something entirely different.

This is the same as an anonymous PL/SQL block in Oracle that you start with DECLARE - everything after that is executed by a different runtime engine on the server. MySQL has no such feature. The only way to run procedural code is to create a procedure (or function), then run that. That's why there is no such thing as DO in MySQL.

The only DBMS product which does not clearly distinguish between procedural code and "plain SQL" is SQL Server: T-SQL is an extension to the SQL language which allows you to mix "regular SQL" and procedural SQL without telling the backend that the code needs a different engine to run (which is a source of great confusion for people migrating from SQL Server to Postgres or Oracle).

SQL/PSM is a standard that defines procedural elements that can be embedded into a database engine that uses SQL as its query language. I know of no DBMS product that actually implements SQL/PSM. Postgres' PL/pgSQL, Oracle's PL/SQL, MySQL's procedural dialect are somewhat similar to that, but far from being compliant with the SQL/PSM standard. I think the closest to the SQL/PSM standard is DB2 and maybe HSQLDB

---

SQL in PostgreSQL" is not the same thing as SQL per SQL standard.

That is true. But then, no DBMS fully implements the SQL standard - but Postgres' implementation is probably one closest to the standard.

PostgreSQL: Same request is slower with plpgsql language compared to sql

First, a general discussion how to get execution plans in such a case

To get to the bottom of that, activate auto_explain and track function execution in postgresql.conf:

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 0
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on
track_functions = 'pl'

Then restart the database. Don't do that on a busy productive database, as it will log a lot and add considerable overhead!

Reset the database statistics with

SELECT pg_stat_reset();

Now the execution plans of all the SQL statements inside your functions will be logged, and PostgreSQL keeps track of function execution times.

Look at the execution plans and execution times of the statements when called from the SQL function and the PL/pgSQL function and see if you can spot a difference. Then compare the execution times in pg_stat_user_functions to compare the function execution time.

Explanation in the current case, after looking at the execution plans

The query run from PL/pgSQL is not parallelized. Due to a limitation in the implementation, queries run with RETURN QUERY never are.

In PostgreSQL, do PL/pgSQL and SQL have different CREATE FUNCTION commands?

The cited definition of CREATE FUNCTION is common for all languages:

lang_name

The name of the language that the function is implemented in. It can be sql, c, internal, or the name of a user-defined procedural language, e.g. plpgsql.

Declare LANGUAGE plpgsql before or after the function body?

No difference whatsoever.

The function body is a string literal. The $$ are just dollar-quotes and could be single quotes, too (but better use dollar quotes!):

• What are '$$' used for in PL/pgSQL
• Insert text with single quotes in PostgreSQL

CREATE FUNCTION is a declarative SQL-DDL command, and the order of keywords is pretty free, as per definition in the manual. (Key words within the curly braces in the command definition can be arranged freely, but not the rest).

I eventually settled on always putting the language declaration on top with the rest of the function header, before the function body. Makes more sense.

What is the difference between prepared statements and SQL or PL/pgSQL functions, in terms of their purpose?

All three "work the same" in that they execute the simple SQL statement:

INSERT INTO foo VALUES (3, 'ben');

The prepared statement is only good for a single prepared SQL statement (as the name suggests). And only DML commands. The manual:

Any SELECT, INSERT, UPDATE, DELETE, or VALUES statement.

The function can contain any number of statements. DML and DDL. Only SQL for SQL functions. Plus some non-SQL procedural elements in PL/pgSQL.

The prepared statement is only visible inside the same session and gone at the end of the session, while the function persists and is visible to all - still only usable for those with the EXECUTE privilege.

The prepared statement is encumbered with the least overhead. (Not much difference.)

The SQL function is the only one of the three that cannot save the query plan (by itself). Read details about plan caching in PL/pgSQL functions in the manual here.

The SQL function is also the only one that could be inlined when used within a bigger query. (Not with an INSERT, though.)

A rather comprehensive list of differences between SQL and PL/pgSQL functions:

• Difference between language sql and language plpgsql in PostgreSQL functions

Starting with Postgres 11 there are also SQL procedures:

• When to use stored procedure / user-defined function?

What does LANGUAGE plpgsql VOLATILE COST 100 mean in functions

LANGUAGE plpgsql VOLATILE COST 100;

LANGUAGE:-programming language used for creating the stored procedure in PostgreSQL.
Here it is plpgsql.

(before going to volatile and cost there is something you need to know first...'query optimizer' for which we are giving these informations. The query optimizer used to determine the most efficient way to execute a given query.)

VOLATILE:-In PostgreSQL Every function has a volatility classification and volatile is one of volatility classification. A VOLATILE function can do anything, including modifying the database. It is default, so it can be omitted.

COST 100:- COST is completely independent from VOLATILE. It declares the cost per row of the result, which is used by the query planner to find the cheapest plan. The default is COST 100 which also can be omitted. Its better to leave it at the default.

In these statement we are giving information's about function to query optimizer. Here it... what language are using and what is the volatility of the function and what is the cost per row of the result.

---

Related Topics