On 12/02/2014 10:40 AM, Nelson Green wrote:
On Tue, Dec 2, 2014 at 11:57 AM, Merlin Moncure <mmoncure@xxxxxxxxx
<mailto:mmoncure@xxxxxxxxx>> 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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
