Skip site navigation (1) Skip section navigation (2)

XPATH function called with Date String

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 (view raw or flat)
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

pgsql-novice by date

Next:From: Didier Gasser-MorlayDate: 2010-01-30 22:20:54
Subject: Re: PpgSQL Autovacuum
Previous:From: Richard TroyDate: 2010-01-28 20:02:46
Subject: Re: SSL Connection help, pls...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group