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

Re: Opening view that uses a function - empty column

From: "David Johnston" <polobo(at)yahoo(dot)com>
To: "'Brady Mathis'" <bmathis(at)r-hsoftware(dot)com>, <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Opening view that uses a function - empty column
Date: 2012-07-30 20:22:04
Message-ID: 021c01cd6e90$fc103900$f430ab00$@yahoo.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Couple of points - though they won't help the current question probably:

>Brady's original
---------------my responses embedded


> -----Original Message-----
> From: pgsql-jdbc-owner(at)postgresql(dot)org [mailto:pgsql-jdbc-
> owner(at)postgresql(dot)org] On Behalf Of Brady Mathis
> Sent: Monday, July 30, 2012 1:38 PM
> To: pgsql-jdbc(at)postgresql(dot)org
> Subject: Re: [JDBC] Opening view that uses a function - empty column
> 
> 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;".

----------------Are you sure that when you connect to the server from
"...the DB server itself" you are connecting to the same database as when
you do so remotely?

----------------This really feels like an identity issue and not anything
specifically related to the code you are showing us.  That said...see below.

> 
> 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 );

------------It is unnecessary to call XMLPARSE twice, especially since you
are doing so on the "no exception" code path.  

> 	EXCEPTION WHEN OTHERS THEN
> 		RETURN XMLPARSE( DOCUMENT '<?xml version="1.0"
> encoding="UTF-8"?><fields></fields>' ); END; $BODY$


------------Whenever you are having difficulties you should avoid ignoring
exceptions.  At worse perform a RAISE NOTICE when one occurs it you still
want to send back an empty document.  However, raising the exception is also
a valid and useful action.  If you are debugging then raising an exception
(and reviewing it) is even more worthwhile.


>   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$


------------Here you call XMLParse/Document on the result of
"get_valid_xml(...)";  this seems redundant since "get_valid_xml(...)"
already does this.


>   LANGUAGE sql VOLATILE
>   COST 100;
> ALTER FUNCTION rhs.extract_from_extended(text, bigint) OWNER TO
> postgres;





In response to

Responses

pgsql-jdbc by date

Next:From: David JohnstonDate: 2012-07-31 00:41:17
Subject: Re: Search path in connection string
Previous:From: Brady MathisDate: 2012-07-30 17:38:02
Subject: Re: Opening view that uses a function - empty column

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