From: | Mazen Abdel-Rahman <saba(dot)mazen(at)gmail(dot)com> |
---|---|
To: | pgsql-novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | XPATH function called with Date String |
Date: | 2010-01-29 17:21:58 |
Message-ID: | 92265adf1001290921r3e75f49ja220fa9f8bf5a077@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Didier Gasser-Morlay | 2010-01-30 22:20:54 | Re: PpgSQL Autovacuum |
Previous Message | Richard Troy | 2010-01-28 20:02:46 | Re: SSL Connection help, pls... |