1

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?

  • What's unclear about it? A function returns a table. One of the columns of that table is calculated from a parameter of the function together with the data from a table existing in the database. The other columns of the returned table are unchanged from the table in the database. I want to be able to run update statements on the returned table. – beerockxs Jul 24 '19 at 13:37
  • But I'm not asking for a practical answer, I'm asking if it's theoretically possible to have a table with a dynamically generated column where the user can update the non-dynamic columns – beerockxs Jul 24 '19 at 13:48
  • Even if it's a theoretical question, an example illustrating the problem may help people to understand your question better (or ensure it's understood correctly). – Andriy M Jul 24 '19 at 14:18
  • 1
    "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." - no you don't you can access the "previous" row using the lag() function. The code you have shown can also be done with one single SQL query. – a_horse_with_no_name Jul 24 '19 at 16:46
  • Regarding the question: you could allow passing additional (optional) parameters to the function that you then can use inside the function to change the computed columns. Of course this is only sensible if the columns and operations you want to allow are limited (e.g. add a constant value to a column or similar things).# – a_horse_with_no_name Jul 24 '19 at 16:47
2

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.

  • I would like my caller to be able to update the static data underlying the dynamically generated set of records, without having to access that table separately. It appears that that's not possible? – beerockxs Jul 24 '19 at 15:17
  • Sorry, thought you just wanted to manipulate the returned table, missed the bit about updating the original table. Edited to something a bit more relevant ;) – Nick Barnes Jul 24 '19 at 16:54
  • Thank you, that works perfectly. – beerockxs Jul 25 '19 at 7:51
  • Except that with the client that's used, I can't set the variable before querying. Damn you, QGIS. – beerockxs Jul 25 '19 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.