Re: Programmatic access to interval units

On Tue, Dec 2, 2014 at 10:16 AM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *I'm pretty sure the interval values are buried in the code, but there is
> nothing to prevent you from creating your own reference table. :) CREATE
> TABLE time_intervals( time_interval_name varchar(15) NOT NULL,
> CONSTRAINT time_intervals_pk PRIMARY KEY (time_interval_name));INSERT INTO
> time_intervalsVALUES('microsecond'),('millisecond'),('second'),('minute'),('hour'),('day'),('week'),('month'),('year'),('decade'),('century'),('millennium');*
>
> *SELECT * FROM time_intervals;*
>

Thanks Melvin,

Actually I've already hard-coded a temporary table into the function so
that I
can move forward with the development, but wanted to make that part more
dynamic, which is what prompted my first question.

Regards,
Nelson

>
> On Tue, Dec 2, 2014 at 10:48 AM, Nelson Green <nelsongreen84(at)gmail(dot)com>
> wrote:
>
>> On Mon, Dec 1, 2014 at 2:14 PM, Merlin Moncure <mmoncure(at)gmail(dot)com>
>> wrote:
>>
>>> On Mon, Dec 1, 2014 at 10:42 AM, Nelson Green <nelsongreen84(at)gmail(dot)com>
>>> wrote:
>>> > Good morning list,
>>> >
>>> > According to the documentation for interval data type inputs, the unit
>>> can
>>> > be one of microsecond, millisecond, second, minute, hour, day, week,
>>> month,
>>> > year, decade, century, or millennium. Are these units stored in a
>>> catalog
>>> > somewhere? I would like to access them programmatically if possible, to
>>> > validate input for a function I am developing.
>>>
>>> if you're writing C, you can use libpqtypes to do this. It exposes the
>>> interval as a C structure.
>>>
>>> typedef struct
>>> {
>>> int years;
>>> int mons;
>>> int days;
>>> int hours;
>>> int mins;
>>> int secs;
>>> int usecs;
>>> } PGinterval;
>>>
>>>
>>> merlin
>>>
>>
>> Thanks Merlin. I am not writing this in C, rather I am using Pl/pgSQL.
>> Apologies
>> for not mentioning that up front. I was hoping to do a SELECT ... WHERE IN
>> query form a catalog relation.
>>
>> That being said, maybe it is time for me to get back into C? I haven't
>> done much
>> in C in many years, but this simple validation function might not be a bad
>> jumping off point. If I do not get the response I was hoping for I may
>> just do
>> that.
>>
>> Regards,
>> Nelson
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.
>