There is a way of implementing what you want by using Regular Expressions and a couple of triggers.
To set the stage, I assume your tables are created this way:
CREATE TABLE entries
entry_id int PRIMARY KEY,
CREATE TABLE entries_kanji
entry_id int REFERENCES entries (entry_id)
I assume that your definition of Kanji can be translated to a Regular Expression. To begin with, I use the definition of Kanji found on Localizing Japan. That is, we can define one funcion to check whether a character is a Kanji by doing this:
CREATE OR REPLACE FUNCTION is_kanji(IN a_char character)
RETURNS boolean AS
a_char ~ '[\x3400-\x4DB5\x4E00-\x9FCB\xF900-\xFA6A]'
~ operator means
text_on_the_left matches the
regular expression on the right. See Pattern Matching in PostgreSQL.
The result of executing the following query
Next step is, given a sentence (a string where there are several Kanji chars), we SELECT all of them. This can be done with the following query:
unnest(regexp_matches('答 answer 電 electricity 教 teach',
'[\x3400-\x4DB5\x4E00-\x9FCB\xF900-\xFA6A]', 'gi')) AS kanji
regexp_matches returns an array containing all the parts of the passed string that match the regular expression. This array is converted into a
set of rows by means of the
Once we have this, we create one trigger function to be associated with INSERTS, UPDATES and DELETES into the
This function would take care:
CREATE FUNCTION trigger_entry()
RETURNS trigger AS $$
if TG_OP = 'DELETE' or TG_OP = 'UPDATE' then
-- When we delete or update rows, we delete all
-- corresponding entries_kanji.
entry_id = OLD.entry_id ;
end if ;
if TG_OP = 'INSERT' or TG_OP = 'UPDATE' then
-- When we insert or update rows, we insert all
-- corresponding entries_kanji
entries_kanji (kanji, entry_id)
'[\x3400-\x4DB5\x4E00-\x9FCB\xF900-\xFA6A]', 'g')) AS kanji,
-- If case we use a 'before' trigger... we return NEW row (untouched)
return new ;
end if ;
-- In case we use a 'before' trigger, ... we return OLD row (untouched).
return old ;
And we associate this trigger function with one trigger (I have chosen it to be an AFTER trigger, in case some other BEFORE triggers do some data manipulation; otherwise, it could also be a BEFORE trigger):
CREATE TRIGGER trigger_make_entries_kanji
AFTER INSERT OR UPDATE OF entry_id, keyword OR DELETE
FOR EACH ROW
EXECUTE PROCEDURE trigger_entry();
With this setup, you can now insert data into
entries, and get the kanji in
entries (entry_id, keyword, reading, meaning)
(1, 'One sentence with one kanji(朗) symbol', 'reading_1', 'meaning_1'),
(2, '答 answer 電 electricity 教 teach', 'reading_2', 'meaning_2');
and see that you get the proper information in the
entry_id, kanji ;
| kanji | entry_id |
| 朗 | 1 |
| 教 | 2 |
| 答 | 2 |
| 電 | 2 |
You can also check that the trigger takes care when you
The advantages of triggers versus materialized views are that
- They change the information in real time, without need for refreshing
- They are available also in older versions of PostgreSQL (which might be important if version is out of your control)
The disadvantages are:
- You (or someone in your team) need to have some knowledge on how to program triggers. Triggers can have some nasty side-effects if not used properly.
- You carry some extra cost when inserting, updating or deleting your table's data.
Depending on your use case, you can decide which solution is more appropriate.