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:27:59
Message-ID: 8027fb62-9fb3-4d94-b8cf-3999005b9c65@googlegroups.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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_id column (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

In response to

pgsql-jdbc by date

Next:From: Brady MathisDate: 2012-07-30 17:38:02
Subject: Re: Opening view that uses a function - empty column
Previous:From: Dave CramerDate: 2012-07-30 14:05:10
Subject: Re: not fetching all query results

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