From: | Peter Geoghegan <peter(dot)geoghegan86(at)gmail(dot)com> |
---|---|
To: | PGSQL Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Getting the typename of a polymorphic function's magical $0 variable |
Date: | 2010-04-22 09:26:48 |
Message-ID: | x2rdb471ace1004220226v47ee346evbec023223fbd8982@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
I've written the following function:
CREATE OR REPLACE FUNCTION indifferent_cast(literal_value text,
type_specification anyelement) RETURNS anyelement AS
$function_body$
-- This function is used when writing migrating scripts and the like.
-- It attempts to cast to the datatype specified by
"type_specification", but, in the event of the cast
-- being unsuccessfuly, it swallows the error and returns NULL
-- example of usage:
-- select indifferent_cast('5391502794050'::text, NULL::gtin)
-- It's useful for migrating data from legacy systems without
appropriate integrity constraints, where a small
-- minority of a particularly field of data will not cast to a desired
datatype (particularly a domain with a check constraint),
-- and you're quite happy to lose this data rather than relax your
constraints or manually correct the legacy data, or you cannot
-- be reasonably expected to correct the malformed data ( as, perhaps,
in the case of a malformed GTIN barcode)
-- It's called indifferent_cast for a reason
-- Clearly, creating a regular cast through CREATE CAST isn't
appropriate for this
DECLARE
BEGIN
$0 := $1;
return $0;
EXCEPTION
WHEN others THEN
RAISE NOTICE 'Failed to perform indifferent_cast';
RETURN NULL;
END;
$function_body$
LANGUAGE 'plpgsql' IMMUTABLE
I would like to be able to RAISE a more appropriate, business domain
level notice, such as 'could not validate barcode' or 'could not
validate e-mail address', based on a CASE statement that checks the
dynamic type of $0 against some likely candidates for my application.
Is it possible to do this? How?
Thanks,
Peter Geoghegan
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2010-04-22 09:31:58 | Re: Getting the typename of a polymorphic function's magical $0 variable |
Previous Message | Wappler, Robert | 2010-04-22 09:25:12 | Re: [GENERAL] Byte order mark added by (the envelope please...) pgAdmin3 !! |