Re: Programmatic access to interval units

[Nelson Green <nelsongreen84@xxxxxxxxx>]

On Tue, Dec 2, 2014 at 10:16 AM, Melvin Davidson <melvin6925@xxxxxxxxx> 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_intervals
VALUES
('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@xxxxxxxxx> wrote:

On Mon, Dec 1, 2014 at 2:14 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:

On Mon, Dec 1, 2014 at 10:42 AM, Nelson Green <nelsongreen84@xxxxxxxxx> 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.