Dear fellow hackers,
Background:
Commit e717a9a "SQL-standard function body" introduced support for
SQL-standard functions, which have two great benefits compared to
plpgsql functions:
*) Dependency tracking
*) Renaming of database objects seamless, thanks to function body being
parsed at function definition time and stored as expression nodes.
Problem:
I really wish I could use such functions more often, but a very common
pattern in my database functions is the need to ensure exactly one row
was returned by a statement, which is currently only achievable via
plpgsql and its INTO STRICT.
I think we just need a way to assert return of a single row per
function, since if needed per statement, we can could just create
separate SQL-functions for each such statement, and execute them
separately, from a single function, if multiple statements are needed
within a single function.
Ideas on possible solutions:
How about piggy-backing on the CREATE FUNCTION's existing ROWS
parameter, and reusing the [ ENFORCED | NOT ENFORCED ] terminology we
have for constraints? We would need to lift the current restriction that
it is only allowed when the function return a set.
CREATE FUNCTION test_update(_a int, _b int)
RETURNS BOOLEAN
ROWS 1 ENFORCED
BEGIN ATOMIC
UPDATE foo SET a = _a WHERE b = _b RETURNING TRUE;
END;
CREATE FUNCTION test_select(_b int)
RETURNS INT
ROWS 1 ENFORCED
BEGIN ATOMIC
SELECT a FROM foo WHERE b = _b;
END;
Alternatively, maybe we could set a per-function GUC,
e.g. SET assert_single_row = true?
CREATE FUNCTION test_update(_a int, _b int)
RETURNS BOOLEAN
SET assert_single_row = true
BEGIN ATOMIC
UPDATE foo SET a = _a WHERE b = _b RETURNING TRUE;
END;
CREATE FUNCTION test_update(_a int, _b int)
RETURNS BOOLEAN
SET assert_single_row = true
BEGIN ATOMIC
SELECT a FROM foo WHERE b = _b;
END;
INSERT and DELETE should be supported as well of course.
These were just the two first ideas on the top of my head, please share
yours if you see a better way.
To me, if we can solve this problem, it would mean a huge improvement in
how I work with database functions in PostgreSQL, since I would then get
the nice benefits of dependency tracking and a more declarative mapping
of how all database objects are connected to functions.
I hope we can solve it together somehow.
/Joel
