Re: Opening view that uses a function - empty column

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