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?
lag()
function. The code you have shown can also be done with one single SQL query.