Re: New Data Type Implementation

On Thu, 2020-09-17 at 18:48 +0200, Franz Hofer wrote:
> I am trying to implement a way (either a new data type (base or composite) or creating C-functions)
> that allow a type to modify the stored data after a specified amount of time.
>
> [...]
>
> An example of all this would be:
> A "Person" table storing the first and last name as column. (For a survey)
> While creating the table, the user selects the above mentioned type as types for the columns.
> Additionally to the type the user has to specify how long that data should be stored / or is valid.
> After the survey is finished (=> or the set date is reached) the data type / logic / or something
> would take care of that and overwrite or reset the values or mark them as invalid.

A data type cannot modify its data, there has to be some process that would do that.
I don't think a data type is the right approach.

I would simply do that when the data are queried, something like

SELECT CASE WHEN expiry_date < current_timestamp THEN NULL::integer ELSE intvalue END
FROM ...

Additionally, you can run a regular clean-up job that deletes or updates expired data,
if you need to physically delete them.

Yours,
Laurenz Albe
--
+43-670-6056265
CYBERTEC PostgreSQL International GmbH
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Web: https://www.cybertec-postgresql.com