Re: [GENERAL] Programmatic access to interval units
On Mon, Dec 1, 2014 at 2:14 PM, Merlin Moncure mmonc...@gmail.com wrote:
On Mon, Dec 1, 2014 at 10:42 AM, Nelson Green nelsongree...@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
Re: [GENERAL] Programmatic access to interval units
*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 nelsongree...@gmail.com
wrote:
On Mon, Dec 1, 2014 at 2:14 PM, Merlin Moncure mmonc...@gmail.com wrote:
On Mon, Dec 1, 2014 at 10:42 AM, Nelson Green nelsongree...@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.
Re: [GENERAL] Programmatic access to interval units
On Tue, Dec 2, 2014 at 9:48 AM, Nelson Green nelsongree...@gmail.com wrote:
On Mon, Dec 1, 2014 at 2:14 PM, Merlin Moncure mmonc...@gmail.com wrote:
On Mon, Dec 1, 2014 at 10:42 AM, Nelson Green nelsongree...@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
Re: [GENERAL] Programmatic access to interval units
On Tue, Dec 2, 2014 at 10:16 AM, Melvin Davidson melvin6...@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 10:48 AM, Nelson Green nelsongree...@gmail.com
wrote:
On Mon, Dec 1, 2014 at 2:14 PM, Merlin Moncure mmonc...@gmail.com
wrote:
On Mon, Dec 1, 2014 at 10:42 AM, Nelson Green nelsongree...@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.
Re: [GENERAL] Programmatic access to interval units
On Tue, Dec 2, 2014 at 11:57 AM, Merlin Moncure mmonc...@gmail.com wrote:
On Tue, Dec 2, 2014 at 9:48 AM, Nelson Green nelsongree...@gmail.com
wrote:
On Mon, Dec 1, 2014 at 2:14 PM, Merlin Moncure mmonc...@gmail.com
wrote:
On Mon, Dec 1, 2014 at 10:42 AM, Nelson Green nelsongree...@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_unitTEXT 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
Re: [GENERAL] 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 mmonc...@gmail.com
mailto:mmonc...@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_unitTEXT 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.kla...@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
Re: [GENERAL] Programmatic access to interval units
On Tue, Dec 2, 2014 at 12:40 PM, Nelson Green nelsongree...@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
Re: [GENERAL] Programmatic access to interval units
On Tue, Dec 2, 2014 at 3:48 PM, Merlin Moncure mmonc...@gmail.com wrote: On Tue, Dec 2, 2014 at 12:40 PM, Nelson Green nelsongree...@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
[GENERAL] Programmatic access to interval units
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. Thanks, Nelson
Re: [GENERAL] Programmatic access to interval units
On Mon, Dec 1, 2014 at 10:42 AM, Nelson Green nelsongree...@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
