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(at)gmail(dot)com
> <mailto:mmoncure(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com