Hi All,
I am using plpgsql's XML type extensively. One of my nodes had a date in it - but it's passed in with the format 'Feb 3, 1975'. When I assign the node text value to a local variable
in the plpgsql function and then take a look at it using 'RAISE NOTICE' it prints out as '1975-02-03'. Is this behavior documented anywhere? Will the xpath function always convert strings that look like dates (I noticed it converts to the same format no matter what date format my xml node is in) to the format 'YYYY-MM-DD'?
Here part of the input XML that's passed in to my main function:
<patient>
<first_name>Peter</first_name>
<last_name>Rabbit</last_name>
<dob>Feb 3, 1975</dob>
<gender>male</gender>
</patient>
v_dob := getNodeTextValue('/patient/dob/text()', patient_info);
getNodeTextValue is a simple helper function that looks like this
-- Function: getNodeTextValue(text,xml)
-- The purpose of this function is to return the text value of a node in text format.
-- The xpath function returns values as arrays - so for each node we would have to
-- first get the array from the xpath function - and then get the first member of
-- that array. This is a helper function that does it for us - so instead of calling
-- xpath we will call getNodeTextValue
--DROP FUNCTION getNodeTextValue(text,xml);
CREATE OR REPLACE FUNCTION getNodeTextValue(xpath_expression text, patient_info xml)
RETURNS text AS
$BODY$DECLARE
array text[1];
result text;
BEGIN
array := xpath(xpath_expression, patient_info);
result := array[1];
return result;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT SECURITY DEFINER
COST 100;
ALTER FUNCTION getNodeTextValue(text,xml) OWNER TO postgres;
Thanks!
Mazen Abdel-Rahman