Postgresql – updateable table from function

postgresql

I have a plpgsql function that returns a table. One of its columns is dynamically generated based on the parameters passed to the function. Is there a possibility to have the user have the ability to update the non-dynamically generated columns of that returned table, like with an instead of update trigger with a view?

Here's a simplified version:
In reality, the function is a lot more complex, and the value of the calculated column for the currently processing row is dependent on the previous rows, which is why I have to use a plpgsql function.

create table test(id integer, startdate date);

CREATE FUNCTION test2 (IN compare_date date)
    RETURNS TABLE(id integer, startdate date, is_before boolean)
    LANGUAGE plpgsql
DECLARE
    rec record;
BEGIN
    FOR rec IN SELECT id, startdate FROM test LOOP
        IF rec.startdate < compare_date THEN
            test2.is_before := true;
        ELSE
            test2.is_before := false;
        END
        test2.id := rec.id;
        test2.startdate := rec.startdate;
        return next;
    END LOOP;
END;

The user just accesses the function, I want him to be able to update the columns in the original table. With a view, this could be done using a trigger, but I can't have dynamically generated columns in a view, can I?

Best Answer

Your function doesn't return a "table" in the usual sense. As noted in the docs, RETURNS TABLE (...) is just a syntactic shortcut for RETURNS SETOF record, plus a bunch of OUT params.

The output of the function is just a collection of values in memory, with no connection to the source of the data, so while callers can transform those values as they please, there is no way to propagate anything back to the original table via the function output.

If you want a different presentation of the underlying data which the user is still able to apply updates to, the only way is with an updatable view. Including generated columns should be no problem, as long as your trigger is written to handle these appropriately. The main obstacle in your case is that, unlike a function, you cannot (directly) pass parameters to a view.

The only workaround that I know of is to define your view in terms of a custom configuration variable, and require that users set this variable before querying the view, e.g.:

CREATE VIEW test2 AS
  SELECT *
  FROM test2(current_setting('my_vars.compare_date')::date);

SET my_vars.compare_date TO '2000-01-01';
SELECT * FROM test2;

Your UPDATE trigger may also query this variable if needed.

Related Question