CODEWITHSUNDEEP
sqldatabasepostgresql
user3620915
user3620915

Reputation: 137

Join three tables postgres?

I've got three tables (PostgreSQL):

PUBBLICATIONS

(pub_id            SERIAL                                   ,
 creation          DATE NOT NULL DEFAULT ('now'::text)::date,
 pub_uuid          CHARACTER VARYING PRIMARY KEY    NOT NULL,
 inv_uuid          CHARACTER VARYING                        )

INVESTIGATIONS

(inv_id            SERIAL                                   ,
 creation          DATE NOT NULL DEFAULT ('now'::text)::date,
 inv_uuid          CHARACTER VARYING PRIMARY KEY    NOT NULL,
 pub_uid           CHARACTER VARYING                        )

RUNDETAILS

(run_id            SERIAL                                   ,
 creation          DATE NOT NULL DEFAULT ('now'::text)::date,
 run_uuid          CHARACTER VARYING PRIMARY KEY    NOT NULL,
 inv_uuid          CHARACTER VARYING REFERENCES investigations(inv_uuid) )

I'd like to create (and update automatically every time that there is a new entry in the DB) a table that contains the *_uuid. Something like:

RUN_INV_PUB

(run_id            SERIAL                                   ,
 creation          DATE NOT NULL DEFAULT ('now'::text)::date,
 run_uuid          CHARACTER VARYING                        ,
 inv_uuid          CHARACTER VARYING                        ,   
 pub_uuid          CHARACTER VARYING                        )

Any suggestions?

Upvotes: 0

Views: 103

Answers (1)

STLDev
STLDev

Reputation: 6154

PostgreSQL supports triggers.

Triggers give a database developer a mechanism to run code when a particular table's contents are modified, such as on insert.

You can write a trigger that would notice the new data inserted (or old date deleted) from your tables, and then insert appropriate data into another table.

You can find an example of this on the PostreSQL site here: PostgreSQL docs - section 36.4 - A Complete Trigger Example.

Upvotes: 1

Related Questions