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?