Message-ID: <4501de283bdb6aa598f733549b69fbf9a92b8c0c.camel@cybertec.at>
Subject: Re: New Data Type Implementation
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Franz Hofer <Franz1231@gmx.at>, pgsql-novice@lists.postgresql.org
Date: Fri, 18 Sep 2020 17:09:08 +0200
In-Reply-To: 
 <trinity-d9d814f4-9dc1-4f16-ab89-d4d27078e392-1600361320157@3c-app-gmx-bs32>
References: 
	<trinity-d9d814f4-9dc1-4f16-ab89-d4d27078e392-1600361320157@3c-app-gmx-bs32>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-1.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Precedence: bulk

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