Обсуждение: Programmatic access to interval units

On Mon, Dec 1, 2014 at 10:42 AM, Nelson Green <nelsongreen84@gmail.com> wrote:
> Good morning list,
>
> According to the documentation for interval data type inputs, the unit can
> be one of microsecond, millisecond, second, minute, hour, day, week, month,
> year, decade, century, or millennium. Are these units stored in a catalog
> somewhere? I would like to access them programmatically if possible, to
> validate input for a function I am developing.

if you're writing C, you can use libpqtypes to do this. It exposes the
interval as a C structure.

typedef struct
{
        int years;
        int mons;
        int days;
        int hours;
        int mins;
        int secs;
        int usecs;
} PGinterval;


merlin

On Tue, Dec 2, 2014 at 9:48 AM, Nelson Green <nelsongreen84@gmail.com> wrote:
> On Mon, Dec 1, 2014 at 2:14 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>> On Mon, Dec 1, 2014 at 10:42 AM, Nelson Green <nelsongreen84@gmail.com>
>> wrote:
>> > Good morning list,
>> >
>> > According to the documentation for interval data type inputs, the unit
>> > can
>> > be one of microsecond, millisecond, second, minute, hour, day, week,
>> > month,
>> > year, decade, century, or millennium. Are these units stored in a
>> > catalog
>> > somewhere? I would like to access them programmatically if possible, to
>> > validate input for a function I am developing.
>>
>> if you're writing C, you can use libpqtypes to do this. It exposes the
>> interval as a C structure.
>>
>> typedef struct
>> {
>>         int years;
>>         int mons;
>>         int days;
>>         int hours;
>>         int mins;
>>         int secs;
>>         int usecs;
>> } PGinterval;
>>
>
> Thanks Merlin. I am not writing this in C, rather I am using Pl/pgSQL.
> Apologies
> for not mentioning that up front. I was hoping to do a SELECT ... WHERE IN
> query form a catalog relation.
>
> That being said, maybe it is time for me to get back into C? I haven't done
> much

well, maybe: that's a different question.  I wasn't sure what exactly
you wanted to verify and how.  The database is coded in C so having a
client side library that exposes the server side data with minimal
translation is pretty valuable.

For an sql solution, you probably want something like this.  It isn't
perfect, because there is some extra calculation happening vs what the
server actually stores but it might suffice:

create or replace function parse_interval(
  _i interval,
  years OUT INT,
  mons OUT INT,
  days OUT INT,
  hours OUT INT,
  mins OUT INT,
  secs OUT INT,
  usecs OUT INT) returns record as
$$
  select
    extract('years' from _i)::INT,
    extract('months' from _i)::INT,
    extract('days' from _i)::INT,
    extract('hours' from _i)::INT,
    extract('minutes' from _i)::INT,
    extract('seconds' from _i)::INT,
    extract('microseconds' from _i)::INT;
$$ language sql immutable;


postgres=# select * from parse_interval('412342 years 5.2314321 months');
 years  │ mons │ days │ hours │ mins │ secs │  usecs
────────┼──────┼──────┼───────┼──────┼──────┼──────────
 412342 │    5 │    6 │    22 │   37 │   52 │ 52003200

merlin

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

On Tue, Dec 2, 2014 at 12:40 PM, Nelson Green <nelsongreen84@gmail.com> wrote:
> 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.

I was the one that was confused -- heh.  I mis-understood the original
email and thought you were trying to validate interval output vs
interval input.

merlin