Обсуждение: Getting the typename of a polymorphic function's magical $0 variable
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
Hello you can use pg_typeof(some) function Regards Pavel Stehule 2010/4/22 Peter Geoghegan <peter.geoghegan86@gmail.com>: > 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
> Hello > > you can use pg_typeof(some) function > > Regards > Pavel Stehule That's great Pavel, thanks a lot. Regards, Peter Geoghegan