On Tue, Dec 2, 2014 at 2:25 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote:
> 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_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 > > >> Regards, >> Nelson >> >> merlin >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
