Reference an arbitrary row and field in another table
Question: Is there any form (data type, inherence..) of implement in postgresql
something like this:

    
    
    CREATE TABLE log (
        datareferenced table_row_column_reference,
        logged boolean
    );
    

The referenced data may be any row field from the database. My objective is
implement something like this without use Procedural Language or implement it
in a higher layer, using only a relational approach and without modify the
rest of the tables. Another feature may be referencial integrity, example:

    
    
    -- Table foo (id,    field1,       field2, fieldn)
    --           ('bar', '2014-01-01', 4.33,   Null)
    
    -- Table log (datareferenced,                              logged)
    --           ({table foo -> id:'bar' -> field2 } <=> 4.33, True)
    
    DELETE FROM foo where id='bar';
    -- as result, on cascade, deleted both rows.
    

I have an application build onto a MVC pattern. The logic is written in
Python. The application is a management tool, very data intensive. My goal is
implement a module that could store additional information per every data
present in the DDBB. Per example, a client have a serie of attributes (name,
address, phone, email ...) across multiple tables, and I want that the app
could store metadata-like for every registry from all the DDBB. A metadata
could be last modification, or a user flag, etc.

I have implemented the metadata model (in postgres), its mapping to objects
and a parcial API. But the part left is the most important, the glue. My plan
B is create that glue in the data mapping layer as a module. Something like
this:

    
    
    address= person.addresses[0]
    address.saveMetadata('foo', 'bar')
    
    -- in the superclass of Address 
    def saveMetadata(self, code, value):
        self.mapper.metadata_adapter.save(self, code, value)
    
    -- in the metadata adapter class:
    def save(self, entity, code, value):
        sql = """update value=%s from metadata_values 
                 where code=%s and idmetadata=
                          (select id from metadata_rels mr
                           where mr.schema=%s and mr.table=%s and 
                           mr.field=%s and mr.recordpk=%s)"""%         
                           (value, code,
                            self.class2data[entity.__class__]["schema"], 
                            self.class2data[entity.__class__]["table"],
                            self.class2data[entity.__class__]["field"],
                            entity.id)
    
        self.mapper.execute(sql)
    
    def read(self, entity , code):
        sql = """select mv.value 
                 from metadata_values mv
                 join metadata_rels mr on mv.idmetadata=mr.id
                 where mv.code=%s and mr.schema=%s and mr.table=%s and 
                       mr.field=%s and mr.recordpk=%s"""%         
                       (code,
                        self.class2data[entity.__class__]["schema"], 
                        self.class2data[entity.__class__]["table"],
                        self.class2data[entity.__class__]["field"],
                        entity.id )
    
        return self.mapper.execute(sql)
    

But it would add overhead between python and postgresql, complicate Python
logic, and using PL and triggers may be very laborious and bug-prone. That is
why i'm looking at doing the same at the DDBB level.


Answer: No, there's nothing like that in PostgreSQL.

You could build triggers yourself to do it, probably using a composite type.
But you've said (for some reason) you don't want to use PL/PgSQL, so you've
ruled that out. Getting RI triggers right is quite hard, though, and you must
apply a trigger to the referencing _and_ referenced ends.

Frankly, this seems like a square peg, round hole kind of problem. Are you
sure PostgreSQL is the right choice for this application?

Describe your needs and goal in context. Why do you want this? What problem
are you trying to solve? Maybe there's a better way to approach the same
problem one step back...

