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 worked last 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 master table
I create with a after-insert trigger a dataset of the slave table with the value "pk master table, 'HEAD'". PK on the slave table is
the master-table-pk & the text. The name must be always uppercase, so I would like to create a rule that shifts the string into
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 dataset on 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@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
