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();