Re: Assert single row returning SQL-standard functions - Mailing list pgsql-hackers

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