postgres function that takes the result of a select query

Question

I'm trying to write a postgres function that takes the result of a select. The detail that requires this use-case is that I'm within the context of a statement-level trigger, so I have the NEW and OLD pseudo-tables, and I'd like to call a function with them as the argument.

CREATE TRIGGER my_trg
AFTER INSERT ON my_table
REFERENCING
    NEW TABLE AS my_inserted_records
FOR EACH STATEMENT
EXECUTE PROCEDURE my_trigger_func();

CREATE FUNCTION my_trigger_func()
RETURNS TRIGGER
AS $$
BEGIN

    SELECT the_other_func(
        (SELECT * FROM my_inserted_records)
    );

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION the_other_func(
   ...what here???...
)
RETURNS JSONB
AS $$
    -- do something
$$ LANGUAGE sql;

I want to be able to call the_other_func from a similar UPDATE trigger, which also contains the pseudo-tables.

Is this possible? Thanks! (I'm using postgres 14)

Answer 1

you can't get there from here, function arguments must be single values, including record types and arrays, but not result-sets or tables.

look for a way to have the the_other_func be a trigger function perhaps using arguments customize its operation for different tables. that will probably be a cleaner solution than the following

As arrays are allowed perhaps you could try an array of records.

CREATE FUNCTION my_trigger_func()
RETURNS TRIGGER
AS $$
BEGIN

    PERFORM the_other_func (array_agg(i)) 
       FROM my_inserted_records AS i ;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

with the otherfunc being defined as.

CREATE FUNCTION the_other_func(
   newdata my_table[]
)
...