Calling functions with exec instead of select
Solution 1:
use PERFORM
statement - http://www.postgresql.org/docs/current/static/plpgsql-statements.html
Sometimes it is useful to evaluate an expression or SELECT query but discard the result, for example when calling a function that has side-effects but no useful result value. To do this in PL/pgSQL, use the PERFORM statement
so it's just
DO $$ BEGIN
PERFORM my_function();
END $$;
Solution 2:
PostgreSQL 11:
PostgreSQL 11 supports true stored procedures as pointed out by @AbdisamadKhalif . They support in-procedure transaction control.
Older versions:
Yes, that's the standard way, and yes it's weird.
Usually you'd write such functions as stored procedures and invoke them with the CALL
or EXECUTE
command. PostgreSQL does not support true stored procedures (multiple result sets, autonomous transactions, and all that) though, only sql-callable user-defined functions.
So the workaround is to SELECT function_name()
using the PostgreSQL extension syntax that omits FROM
, or SELECT 1 FROM function_name();
to be (somewhat) more standard.
The ODBC driver, JDBC driver, etc understand the {call func_name()}
escape syntax and automatically translate it to an underlying SELECT
.
Solution 3:
You will use from
when the function returns a set. If the function returns void
just do
select my_function();