Thread: Type OID search does not support search_path and multiple schemas

One of the possible solutions, would be to use select 'schema_name.type_name'::regtype::oid to get the OID. But as the lookup is now done without using schema name this method is not easy to be used with the current implementation of the TypeInfoCache...

My patch of the file org/postgresql/jdbc2/TypeInfoCache.java, that would work for search_path's that contain schema names without " and , charachters (and version lookup is not really done quite nicely as I am connecting now only to 9.0 version of the DB... Anyway it is better, than a current implementation:

190,203c190,192
<             String sql;
<             
<             if (_conn.haveMinimumServerVersion("8.4")) {
<                 sql = "select distinct on ( typname ) typinput='array_in'::regproc, typtype\n" +
<                       "  from pg_type as t \n" +
<                       "  join pg_namespace as n on typnamespace = n.oid \n" +
<                       "  left join ( select row_number() over() as rank, \n" +
<                       "                     case when n = '\"$user\"' then current_user else btrim(n,' \"') end as schema_name \n" +
<                       "                from regexp_split_to_table(current_setting('search_path'), E', ?' ) as r(n) ) as r on nspname = schema_name \n" +
<                       " where typname = ? \n" +
<                       " order by typname, rank nulls last, oid desc";
<             } else {
<                 // actually this is not correct, but we do not care for that old versions now
<                 sql = "SELECT typinput='array_in'::regproc, typtype FROM pg_type WHERE typname = ? order by oid desc";
---
>             String sql = "SELECT typinput='array_in'::regproc, typtype FROM ";
>             if (_conn.haveMinimumServerVersion("7.3")) {
>                 sql += "pg_catalog.";
204a194
>             sql += "pg_type WHERE typname = ?";
248,256c238,239
<             if (_conn.haveMinimumServerVersion("8.4")) {
<                 sql = "select distinct on ( typname ) t.oid\n" +
<                       "  from pg_type as t \n" +
<                       "  join pg_namespace as n on typnamespace = n.oid \n" +
<                       "  left join ( select row_number() over() as rank, \n" +
<                       "                     case when n = '\"$user\"' then current_user else btrim(n, ' \"') end as schema_name \n" +
<                       "                from regexp_split_to_table(current_setting('search_path'), E', ?' ) as r(n) ) as r on nspname = schema_name \n" +
<                       " where typname = ? \n" +
<                       " order by typname, rank nulls last, oid desc";
---
>             if (_conn.haveMinimumServerVersion("7.3")) {
>                 sql = "SELECT oid FROM pg_catalog.pg_type WHERE typname = ?";

Please consider the environment before printing this email.


E-mail messages may contain viruses, worms, or other malicious code. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective action against such code. Henry Schein is not liable for any loss or damage arising from this message.

The information in this email is confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this e-mail by anyone else is unauthorized.