Re: [Solved] Programmatic access to interval units

On Tue, Dec 2, 2014 at 2:25 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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
>

Thanks Adrian, for putting my head back on straight.

Not only would that be at least as easy, I have done similar error trapping
in
other functions. Not to sure how I got off on this tangent and then stuck
with
it. Guess I was trying to make this way harder than it needed to be, or I
had
way too much turkey over the past holiday?

And a big thanks to everyone that took time to work with me too.

Regards,
Nelson

>
>
>> Regards,
>> Nelson
>>
>> merlin
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>