Re: Programmatic access to interval units

On Tue, Dec 2, 2014 at 11:57 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> 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.

Hi Merlin,

I'm afraid I'm only confusing things, so let me give an example of what I am
trying to do:

-- Example
--------------------------------------------------------------------
CREATE OR REPLACE FUNCTION check_interval(_period TEXT, _unit TEXT)
RETURNS INTERVAL
AS $$
DECLARE
_DEFAULT_INTERVAL INTERVAL := '1 HOUR';

BEGIN
-- Create a temporary table that maintains the time intervals:
CREATE TEMPORARY TABLE interval_period
(
interval_unit TEXT NOT NULL
);

INSERT INTO interval_period
VALUES
('microsecond'),
('microseconds'),
('millisecond'),
('milliseconds'),
('second'),
('seconds'),
('minute'),
('minutes'),
('hour'),
('hours'),
('day'),
('days'),
('week'),
('weeks'),
('month'),
('months'),
('year'),
('years'),
('decade'),
('decades'),
('century'),
('centurys'),
('millennium'),
('millenniums');

IF _period !~ '[1-9]\d*'
THEN
DROP TABLE interval_period;
RETURN _DEFAULT_INTERVAL;
END IF;

IF LOWER(_unit) NOT IN (SELECT interval_unit
FROM interval_period)
THEN
DROP TABLE interval_period;
RETURN _DEFAULT_INTERVAL;
END IF;

DROP TABLE interval_period;
RETURN CAST(CONCAT(_period, _unit) AS INTERVAL);

END;
$$
LANGUAGE PLPGSQL;
-- End Example
----------------------------------------------------------------

In the line: IF LOWER(_unit) NOT IN (SELECT interval_unit ..., I would
rather
query a catalog table for the interval unit names if possible. That would
then
compensate for any changes to those values in the future.

When I meant do this in C, I was referring to rewriting this function in C
instead of Pl/pgSQL.

I hope this helps you understand what I am asking, and apologies for not
being
more specific up front.

Regards,
Nelson

merlin
>