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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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;

merlin

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
in C in many years, but this simple validation function might not be a bad
jumping off point. If I do not get the response I was hoping for I may just
do
that.

Regards,
Nelson

*I'm pretty sure the interval values are buried in the code, but there is
nothing to prevent you from creating your own reference table. :) CREATE
TABLE time_intervals( time_interval_name varchar(15) NOT NULL,
CONSTRAINT time_intervals_pk PRIMARY KEY (time_interval_name));INSERT INTO
time_intervalsVALUES('microsecond'),('millisecond'),('second'),('minute'),('hour'),('day'),('week'),('month'),('year'),('decade'),('century'),('millennium');*

*SELECT * FROM time_intervals;*

On Tue, Dec 2, 2014 at 10: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;

merlin

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
in C in many years, but this simple validation function might not be a bad
jumping off point. If I do not get the response I was hoping for I may
just do
that.

Regards,
Nelson

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Dec 2, 2014 at 10:16 AM, Melvin Davidson <melvin6925@gmail.com>
wrote:

*I'm pretty sure the interval values are buried in the code, but there is
nothing to prevent you from creating your own reference table. :) CREATE
TABLE time_intervals( time_interval_name varchar(15) NOT NULL,
CONSTRAINT time_intervals_pk PRIMARY KEY (time_interval_name));INSERT INTO
time_intervalsVALUES('microsecond'),('millisecond'),('second'),('minute'),('hour'),('day'),('week'),('month'),('year'),('decade'),('century'),('millennium');*

*SELECT * FROM time_intervals;*

Thanks Melvin,

Actually I've already hard-coded a temporary table into the function so
that I
can move forward with the development, but wanted to make that part more
dynamic, which is what prompted my first question.

Regards,
Nelson

On Tue, Dec 2, 2014 at 11:57 AM, Merlin Moncure <mmoncure@gmail.com> wrote:

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.

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.

Regards,
Nelson

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Dec 2, 2014 at 2:25 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Dec 2, 2014 at 3:48 PM, Merlin Moncure <mmoncure@gmail.com> wrote:

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

But you took time to work with me, and I appreciate that.

Thanks,
Nelson