Re: Programmatic access to interval units

On Tue, Dec 2, 2014 at 9: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;
>>
>
> 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

well, maybe: that's a different question. I wasn't sure what exactly
you wanted to verify and how. The database is coded in C so having a
client side library that exposes the server side data with minimal
translation is pretty valuable.

For an sql solution, you probably want something like this. It isn't
perfect, because there is some extra calculation happening vs what the
server actually stores but it might suffice:

create or replace function parse_interval(
_i interval,
years OUT INT,
mons OUT INT,
days OUT INT,
hours OUT INT,
mins OUT INT,
secs OUT INT,
usecs OUT INT) returns record as
$$
select
extract('years' from _i)::INT,
extract('months' from _i)::INT,
extract('days' from _i)::INT,
extract('hours' from _i)::INT,
extract('minutes' from _i)::INT,
extract('seconds' from _i)::INT,
extract('microseconds' from _i)::INT;
$$ language sql immutable;

postgres=# select * from parse_interval('412342 years 5.2314321 months');
years │ mons │ days │ hours │ mins │ secs │ usecs
────────┼──────┼──────┼───────┼──────┼──────┼──────────
412342 │ 5 │ 6 │ 22 │ 37 │ 52 │ 52003200

merlin