rule / trigger definition

From: Philipp Kraus <philipp(dot)kraus(at)flashpixx(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: rule / trigger definition
Date: 2012-12-22 15:32:20
Message-ID: 2DCBB059-8322-4C09-9F44-FED09E40E68B@flashpixx.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Thanks

Phil

Responses

Browse pgsql-general by date

  From Date Subject
Next Message jo 2012-12-22 17:29:51 dump order by
Previous Message ERR ORR 2012-12-22 15:24:27 Question on Trigram GIST indexes