Re: [Solved] Programmatic access to interval units - Mailing list pgsql-general

On 12/02/2014 10:40 AM, Nelson Green wrote:

On Tue, Dec 2, 2014 at 11:57 AM, Merlin Moncure <mmoncure@gmail.com
<mailto:mmoncure@gmail.com>> wrote:

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.

Would it not be easier to just try the CAST and then catch the exception and handle it:

http://www.postgresql.org/docs/9.3/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

Regards,
Nelson

    merlin

--
Adrian Klaver
adrian.klaver@aklaver.com