Thread: Opening view that uses a function - empty column
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
You'd have to give us a bit more information such as how you "open" it using JDBC Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Fri, Jul 27, 2012 at 3:46 PM, Brady Mathis <bmathis@r-hsoftware.com> 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 > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc
I am actually using Jasper reports to access the view. I created the data source and specified the following: Driver: org.postgresql.Driver URL: jdbc:postgresql://localhost:5432/postgres UID and PW I made sure to update to postgresql-9.1-902.jdbc4.jar (PG version is 9.1.2) Then I defined a query simply as: select * from encounter; I also tried the same query using a SquirrelSQL client (just to eliminate a problem in Jasper) and got the same result. The column filled by the function is empty. If I select * from encounter directly in postgres, the column is filled. My apologies if I haven't included the implementation details that you need. Thanks!
On Jul 27, 2012, at 17:21, Brady Mathis <bmathis@r-hsoftware.com> wrote: > I am actually using Jasper reports to access the view. I created the data source and specified the following: > > Driver: org.postgresql.Driver > URL: jdbc:postgresql://localhost:5432/postgres > UID and PW > > I made sure to update to postgresql-9.1-902.jdbc4.jar (PG version is 9.1.2) > > Then I defined a query simply as: > > select * from encounter; > > I also tried the same query using a SquirrelSQL client (just to eliminate a problem in Jasper) and got the same result. The column filled by the function is empty. > > If I select * from encounter directly in postgres, the column is filled. > > My apologies if I haven't included the implementation details that you need. > > Thanks! > Is the same user being used in both cases and are there any other functions with the same name but in other schemas? David J.
Oh! And, I made another observation while cracking away at this: When I connect to the DB using PGAdmin (1.14.3) from
aremote workstation as opposed to on the DB server itself, I see the same problem with the empty external_id column
whenexecuting "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
thecolumn - 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[@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
Thanks for your replies.
David J. - Yes, the same User/PW is used to access, and no, there aren't any other functions in other schemas.
I wrote some simple java code to test the issue (below). The connection works and the table rows are returned, but the
external_idcolumn (filled by the function) is not populated.
public class testconn {
public static void main(String[] argv) {
try {
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
return;
}
Connection connection = null;
try {
String url = "jdbc:postgresql://localhost:5432/postgres";
Properties props = new Properties();
props.setProperty("user","postgres");
props.setProperty("password","postgres");
connection = DriverManager.getConnection(url, props);
} catch (SQLException e) {
e.printStackTrace();
return;
}
try {
Statement st = connection.createStatement();
ResultSet rs = st.executeQuery("SELECT external_id FROM rhs.encounter");
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Thanks,
Brady
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
Couple of points - though they won't help the current question probably:
>Brady's original
---------------my responses embedded
> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
> owner@postgresql.org] On Behalf Of Brady Mathis
> Sent: Monday, July 30, 2012 1:38 PM
> To: pgsql-jdbc@postgresql.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[@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;
David J -
Thanks for the pointers. I'm sure you can tell that these are (nearly) the first PG procedures that I have written. I
didthe suggested refactoring.
Could you elaborate a little on the "identity" problem?
There is only one set of schemas on the server against which I am testing. I am using the same credentials to log in
viaPGAdmin (local and remote) and Java.
The only non-default configuration I have for PG (that I can think of now) are a few entries in pg_hba.conf as follows:
host all all 127.0.0.1/32 md5
host postgres postgres 173.20.10.3/32 md5
hostssl postgres postgres 127.0.0.1/32 md5
hostssl postgres postgres 173.20.10.3/32 md5
I appreciate all your help,
Brady
On Monday, July 30, 2012 2:22:04 PM UTC-6, "David Johnston" wrote:
> Couple of points - though they won't help the current question probably:
>
>
>
> >Brady's original
>
> ---------------my responses embedded
>
>
>
>
>
> > -----Original Message-----
>
> > From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-
>
> > owner@postgresql.org] On Behalf Of Brady Mathis
>
> > Sent: Monday, July 30, 2012 1:38 PM
>
> > To: pgsql-jdbc@postgresql.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[@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;
>
>
>
>
>
>
>
>
>
>
>
> --
>
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>
> To make changes to your subscription:
>
> http://www.postgresql.org/mailpref/pgsql-jdbc