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