PostgreSQL view to read another table, transform data and return results

Could some please suggest the best way to implement this:

Essentially what I want to have is a virtual-table in PostgreSQL that when queried, it looks up the data from some other table (call this the raw-data table). But the catch is, the data from the raw-data table needs to transformed, and when done so, the results are returned as if it were in the original table.

I will explain with illustrations and an example (not my real program, but just a simplified example to explain the main things I need):

Raw-data Table

| id (int) | data (byte[]) |
----------------------------
|   ...    |      ...      |
|   ...    |      ...      |

Person Table (virtual table)

| name (varchar) | address (varchar) | phone (varchar) |
--------------------------------------------------------
|       ...      |        ...        |       ...       |
|       ...      |        ...        |       ...       |
|       ...      |        ...        |       ...       |

What I now want to have is, that when I do something like:

SELECT * FROM person WHERE name = 'Kim';

• Data from the raw table should be queried, i.e. get all byte[] arrays;
• Deserialize the data to get (name, address开发者_开发知识库, phone) from raw data;
• Apply any conditions, i.e. name = 'Kim'
• Return this set of results to user as if they just simply queried the Person table.

For de-serialisation/transformation etc, I am guessing I may need some programming language functions, so for Java something such as Pl/Java may be useful?

Also, you can assume the Raw-data table will not be very long, it’s implemented to act as a fixed size buffer, so it’s not much of a problem to read all entries for each query.

Conceptually, what I need is a trigger function, but seems we cannot have a trigger on READ. Any help about how I can go about this would be appreciated.

That database design seems totally broken to me. You are trying to re-implement a RDBMS by mis-using a RDBMS.

But if you insist on using this design, the only way I can think of is to write a set returning function that does all the processing and unpacking of the raw data in a procedural way. Depending on the complexity of your binary data, PL/pgSQL might be enough.

You would then do a SELECT * FROM retrieve_person() to get the output you want.