0

I want (for some reason) to store data as a bytea (binary blob, let's say it's a C struct memory dump, or an ASN.1 encoded record, as dumb and a bad idea as it sounds) and allow access to it via a user-defined types (coded in C, calling the deserialiser, filling the UDT fields).

I'd like to keep them (bytea and UDT) in the same table or at least to be able to access the data in both ways (through the original bytea, and through the UDT). And I'd like the UDT not to take space (I want it to be deserialised from the bytea anytime it is accessed).

For example I'd declare a simple 2-column table : (bytea, my_udt). How can I implement the UDT so it knows it's backed by the bytea field? Or other option, if I create just one table with the bytea, then is it possible to create a view from the bytea, allowing access to the bytea AND the UDT extracted on-the-fly when needed (but doesn't take disk space)?

Is it possible at all? How should I proceed?

Touisteur EmporteUneVache
  • 111
  • 1
  • 8
  • 2
    A view seems to be the best choice here. In Postgres 12 you could use a computed column but that is stored (persisted), so doesn't match your requirement "not to take space" –  Dec 15 '19 at 08:23
  • 1
    [Functions can emulate "computed fields"](https://www.postgresql.org/docs/current/sql-expressions.html#SQL-EXPRESSIONS-FUNCTION-CALLS) as well – Bergi Dec 15 '19 at 13:59
  • Hey @a_horse_with_no_name it looks like a *virtual* computed column would 'not take space' (sorry my wording was very imprecise, I wasn't sure how to convey this aspect). Now I'm not sure it can contain a UDT, and not sure how it would go. Seems also that a table with a generated field can't be partitioned. – Touisteur EmporteUneVache Dec 15 '19 at 15:55
  • Now I'm not sure how a view would do this? CREATE VIEW decoded_T (decoded) AS SELECT decode(r) as decoded with r in T; ? Is this what you had in mind? I think it would do the trick. – Touisteur EmporteUneVache Dec 15 '19 at 16:11
  • It appears that for the time being there are no 'virtual' generated columns in Postgres 12. The original patch (https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=fc22b6623b6b3bab3cb057ccd282c2bfad1a0b30) talks about maybe having them in the future but can find anything since in Postgres' git... So I'm guessing virtual views it is. – Touisteur EmporteUneVache Dec 15 '19 at 18:30

0 Answers0