Re: Can I wrtie a function that has a BEFORE trigger that is not column name dpendent?

On 9/14/19 12:44 PM, stan wrote:

Please reply to list also.
Ccing list.

> On Sat, Sep 14, 2019 at 11:50:08AM -0700, Adrian Klaver wrote:
>> On 9/14/19 11:33 AM, stan wrote:
>>> I would like to write a generic function that I can place as a BEFORE trigger
>>> on several tables. It would do a max() on the column it was triggered for, and
>>> return(max + 1) unless max returns a NULL, in which case it would return one.
>>>
>>> Yes, I know this looks a lot like a sequence, but normally this value would
>>> be provided manually at row input time, I just want to allow for some
>>> automated updates.
>>>
>>> The trick is hat the function needs to work for any numeric column I place it n
>>> as a trigger.
>>>
>>
>> What procedural language?
>>
>> Triggers are placed on tables not columns so you will need some way of
>> identifying the column(s) in the table. Trigger functions can take arguments
>> even though they are not declared in the function creation. So that is one
>> way of defining the column.
>>
>
> OK, so maybe I cna declare trigers that are specfic to the table/column, and
> use them to pass the column name to the function.

A trigger is specific to a table. The function it invokes can accept
arguments that help narrow it's focus.

It is spelled out here:

https://www.postgresql.org/docs/11/sql-createtrigger.html

and if you are using plpgsql here:

https://www.postgresql.org/docs/11/plpgsql-trigger.html

To build dynamic SQL that operates on the arguments or other table
specific operations you can use(again plpgsql specific):

https://www.postgresql.org/docs/11/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com