Re: Programmatic access to interval units

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