Re: Programmatic access to interval units

[Nelson Green <nelsongreen84@xxxxxxxxx>]

On Tue, Dec 2, 2014 at 11:57 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:

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