Am very new in Database development so I have some doubts regarding my following example:
Function f1() - language sql
create or replace function f1(istr varchar) returns text as $$
select 'hello! '::varchar || istr;
$$ language sql;
Function f2() - language plpgsql
create or replace function f2(istr varchar)
returns text as $$
begin select 'hello! '::varchar || istr; end;
$$ language plpgsql;
Both functions can be called like select f1('world')
or select f2('world')
.
If I call select f1('world')
the output will be:
`hello! world`
And output for select f2('world')
:
ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function f11(character varying) line 2 at SQL statement ****** Error ******
I wish to know the difference and in which situations I should use language sql
or language plpgsql
.
Any useful link or answers regarding functions will much appreciated.
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.
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.
Here is a thread on pgsql-performance discussing some of these items:
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:
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: