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

Re: Opening view that uses a function - empty column

From: Brady Mathis <bmathis(at)r-hsoftware(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Opening view that uses a function - empty column
Date: 2012-07-30 17:38:02
Message-ID: 303846e6-18ea-47c7-ab9d-83ad583bbc8d@googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Oh!  And, I made another observation while cracking away at this:  When I connect to the DB using PGAdmin (1.14.3) from a remote workstation as opposed to on the DB server itself, I see the same problem with the empty external_id column when executing "select * from rhs.encounter;".

Could this be a problem with how I have implemented my functions?  There are two  functions calls actually used to fill the column - I included the complete text of the functions below.

get_valid_xml - ensures that content from pg_largeobject is valid xml
extract_from_extended - uses xmlparse and xpath to get one specific value

Thanks...again!

/* Function to validate xml for xpath use in SQL query */
CREATE OR REPLACE FUNCTION rhs.get_valid_xml(x text)
  RETURNS xml AS
$BODY$
BEGIN
	PERFORM XMLPARSE( DOCUMENT x );
		RETURN XMLPARSE( DOCUMENT x );
	EXCEPTION WHEN OTHERS THEN
		RETURN XMLPARSE( DOCUMENT '<?xml version="1.0" encoding="UTF-8"?><fields></fields>' );
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION rhs.get_valid_xml(text) OWNER TO postgres;


/* Function to allow extract of fields from _extended */
CREATE OR REPLACE FUNCTION rhs.extract_from_extended(field_name text, menu_data_id bigint)
  RETURNS text AS
$BODY$
	select translate( xpath('//field[(at)name="'||$1||'" and @type="String"]/text()', xmlparse(document ( rhs.get_valid_xml( array_to_string( array(select lo.data from app.menu_data md, pg_catalog.pg_largeobject lo where md.id = $2 and  md.xml01 = lo.loid), '') ) )))::text, '{}', '');
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION rhs.extract_from_extended(text, bigint) OWNER TO postgres;




On Friday, July 27, 2012 1:46:20 PM UTC-6, Brady Mathis wrote:
> Two step inquiry:
> 
> 
> 
> 
> 
> 1)  I have created a function that will parse a value out of XML stored in the pg_largeobject table using xpath, like so:
> 
> 
> 
> 
> 
> CREATE OR REPLACE FUNCTION extract_from_extended(text, bigint)
> 
>   RETURNS text AS
> 
> $BODY$
> 
> 	select translate( xpath(...) );
> 
> $BODY$
> 
>   LANGUAGE sql VOLATILE
> 
>   COST 100;
> 
> 
> 
> 
> 
> 2)  I then created a view that calls this function as part of the query, like so:
> 
> 
> 
> 
> 
> CREATE OR REPLACE VIEW encounter AS 
> 
> SELECT md.account_id, extract_from_extended('externalEncounterID'::text, md.id) AS external_id...
> 
> 	FROM app.menu_data md, app.menu_structure ms
> 
> 	WHERE...;
> 
> 
> 
> When I open this view via JDBC, there is no data in the column that is filled by the function call.
> 
> 
> 
> Any thoughts?
> 
> TIA, 
> 
> BRady

In response to

Responses

pgsql-jdbc by date

Next:From: David JohnstonDate: 2012-07-30 20:22:04
Subject: Re: Opening view that uses a function - empty column
Previous:From: Brady MathisDate: 2012-07-30 17:27:59
Subject: Re: Opening view that uses a function - empty column

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