Re: Programmatic access to interval units

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