New Data Type Implementation

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

New Data Type Implementation

Franz Hofer
Hello,
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.
If I create a new Base Type, at minimum I have to provide input_function and output_function.
While I would be able to accomplish that, I cannot get my head around on where/how I will have to do the checks considering the time (how long those values are valid). And where/how I might overwrite and update the stored values.
 
Is it possible to create a new base type that can do such things? Or would I have to create a client app that parses query input from the user, then makes some checks (or does them upfront / on startup on a daily basis), or calls a SQL function which will check and update affected columns?
If I create a new base type, I thought I would use type modifiers, which can take 2 integers as input. And then use those values to calculate the date until those values are valid to. (ex: Unix time). Maybe using the first int for the date and the second for time. (I would provide a function that does the conversion, so no user would ponder - I know it's probably not the best way, but right know I can't really think about any other way)
 
Or would a composite type which stores the value and the date alongside "better"?
 
 
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.
 
 
For finding out which typmod a table uses I could use following query (which needs to be adapted to search all tables)
 
SELECT attname, atttypmod, format_type(atttypid, atttypmod) AS type
FROM   pg_attribute
WHERE  attrelid = 'testtable'::regclass
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum;
 
Output:
attname            |  atttypmod  |               type
------------------------------------------------------------------------
"first_column"   |      104        |    "character varying(100)"
"custom"           |         -1        |    "CustomCompositeType"
 
Another topic I hope you know the answer to is: If I create an extension.... Is there a way to query the database within a C-function?
When creating a client, I'll have to create a connection object, connect to the database and then execute queries. How would I accomplish this in an extension? I couldn't find information about this.
 
 
Can anyone point me towards something / or a direction on where I can find additional info?
 
 
Hopefully someone can help me.
Best Regards
Franz
Reply | Threaded
Open this post in threaded view
|

Re: New Data Type Implementation

Laurenz Albe
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