From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Nelson Green <nelsongreen84(at)gmail(dot)com>, pgsql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Programmatic access to interval units |
Date: | 2014-12-02 20:25:20 |
Message-ID: | 547E2030.4090500@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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:
>
> Regards,
> Nelson
>
> merlin
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Davide S | 2014-12-02 20:32:31 | Re: JSON_AGG produces extra square brakets |
Previous Message | Day, David | 2014-12-02 19:16:13 | segmentation fault postgres 9.3.5 core dump perlu related ? |