Обсуждение: Can I wrtie a function that has a BEFORE trigger that is not columnname dpendent?

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.


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

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.

> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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.klaver@aklaver.com