| From: | Franz Hofer <Franz1231(at)gmx(dot)at> |
|---|---|
| To: | pgsql-novice(at)lists(dot)postgresql(dot)org |
| Subject: | New Data Type Implementation |
| Date: | 2020-09-17 16:48:40 |
| Message-ID: | trinity-d9d814f4-9dc1-4f16-ab89-d4d27078e392-1600361320157@3c-app-gmx-bs32 |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-novice |
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>Hello,</div>
<div>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.</div>
<div>If I create a new Base Type, at minimum I have to provide <tt class="REPLACEABLE c2">input_function</tt> and <tt class="REPLACEABLE c2">output_function.</tt></div>
<div>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.<br/>
</div>
<div>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?</div>
<div>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)</div>
<div> </div>
<div>Or would a composite type which stores the value and the date alongside "better"?</div>
<div> </div>
<div> </div>
<div>
<div>An example of all this would be:</div>
<div>A "Person" table storing the first and last name as column. (For a survey)</div>
<div>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.</div>
<div>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.</div>
<div> </div>
<div> </div>
<div>For finding out which typmod a table uses I could use following query (which needs to be adapted to search all tables)</div>
<div>(credits to: <a href="https://dba.stackexchange.com/questions/75015/query-to-return-output-column-names-and-data-types-of-a-query-table-or-view" target="_blank">https://dba.stackexchange.com/questions/75015/query-to-return-output-column-names-and-data-types-of-a-query-table-or-view</a>)</div>
<div> </div>
<div>SELECT attname, atttypmod, format_type(atttypid, atttypmod) AS type<br/>
FROM pg_attribute<br/>
WHERE attrelid = 'testtable'::regclass<br/>
AND attnum > 0<br/>
AND NOT attisdropped<br/>
ORDER BY attnum;</div>
<div> </div>
<div>Output:</div>
<div>attname | atttypmod | type</div>
<div>------------------------------------------------------------------------</div>
<div>"first_column" | 104 | "character varying(100)"<br/>
"custom" | -1 | "CustomCompositeType"</div>
</div>
<div> </div>
<div>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?</div>
<div>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.<br/>
</div>
<div> </div>
<div>Can anyone point me towards something / or a direction on where I can find additional info?</div>
<div> </div>
<div> </div>
<div>Hopefully someone can help me.</div>
<div>Best Regards</div>
<div>Franz</div></div></body></html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 4.2 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Laurenz Albe | 2020-09-18 15:09:08 | Re: New Data Type Implementation |
| Previous Message | Keith | 2020-09-15 16:27:22 | Re: Open version 9.3 .out file with version 12 |