Re: rule / trigger definition - Mailing list pgsql-general

On 12/22/2012 07:32 AM, Philipp Kraus wrote:
> Hello,
>
> I'm aktually create a database with postgres, but I'm a long time out-of-date working with Postgres (I think I have
workedlast time with PG 5.4), 
> so I need some help to create a working trigger / rule solution:
>
> I have got a master table and a slave table (1:N). The master table create a key with a sequence, on an insert on the
mastertable 
> I create with a after-insert trigger a dataset of the slave table with the value "pk master table, 'HEAD'". PK on the
slavetable is 
> the master-table-pk & the text. The name must be always uppercase, so I would like to create a rule that shifts the
stringinto 
> uppercase letters, but if I create the rule and add INSEAD ("UPPER"(new.positionname)) I get a syntax error.
>
> At next the dataset with the HEAD value need not be delete or changed of a corresponding dataset on the master table
exists.I have create two 
> rules for update & delete with a do-nothing if the value is HEAD. But I don't know how can I delete the value if the
dataseton the master table 
> is deleted.
>
> Which case is the best choice to do this? Should I use trigger or rules? I need this:
>
> if a dataset is insert on the master table, a new dataset must be create on the slave table with the correct key
value
> if a dataset on the slave table is updated or delete which has the name "HEAD" the action need not be performed
> if a dataset on the master table is deleted all datasets on the slave table must be also deleted.
> the key field on the slave table must be always upper case (index & content value)
>
> Hope anybody can send me some tips

Not sure I entirely follow but here it goes.

1) Stay away from rules if at all possible. Trying to track the flow of
data through them is almost guaranteed to give you massive headaches.

2) You can use AFTER INSERT/UPDATE triggers to do what you want. Just do
the uppercase in trigger on the values you will be passing to the slave
table.

3) The DELETE portion can be handled by logic in the trigger function as
well. The part I am not sure about is line 2 where you say the
update/delete can happen on the slave. This would need a UPDATE/DELETE
trigger on the slave.

4) Deleting all the slave datasets on delete of the master record would
seem to be something handled by a FOREIGN KEY with ON DELETE CASCADE.
Would need to see the actual table schema to clarify.

5) You can create a single function to handle most of these tasks. In
PL/pgsql you can use TG_OP to sort out INSERT/UPDATE/DELETE

http://www.postgresql.org/docs/9.2/interactive/plpgsql-trigger.html

In PL/Pythonu it is TD["event"]

http://www.postgresql.org/docs/9.2/interactive/plpython-trigger.html


>
> Thanks
>
> Phil
>


--
Adrian Klaver
adrian.klaver@gmail.com