Re: obj_description problems?

Ooer - it is a nasty bug.  From pg_proc.h, the definition of
obj_description is:

select description from pg_description where objoid = $1 and classoid =
(select oid from pg_class where relname = $2 and relnamespace = PGNSP)
and objsubid = 0

And what's more, none of the SQL functions in pg_proc.h are properly
qualified.  I have attached a patch that may or may not be the solution
- please check.

I didn't know how to handle 'timestamp without time zone' types and
'overlaps'.

I realise now that there's no need to schema-qualify names - you can
only do names from pg_catalog.

Chris


Christopher Kings-Lynne wrote:

> How do I use a schema-qualified name in obj_description?  Or is this a
> nsty little bug?
>
> Chris
>
> test2=# create schema myschema;
> CREATE SCHEMA
> test2=# create table myschema.pg_class (a int4);
> CREATE TABLE
> test2=# select oid from pg_catalog.pg_class where
> oid='myschema.pg_class'::regclass;
>    oid
> ---------
>  1475161
> (1 row)
>
> test2=# select obj_description('1475161', 'pg_class');
>  obj_description
> -----------------
>
> (1 row)
>
> test2=# select obj_description('1475161', 'pg_catalog.pg_class');
>  obj_description
> -----------------
>
> (1 row)
>
> test2=# set search_path to myschema, pg_catalog;
> SET
> test2=# select obj_description('1475161', 'pg_class');
> ERROR:  Attribute "relname" not found
> test2=# select obj_description('1475161', 'pg_catalog.pg_class');
> ERROR:  Attribute "relname" not found
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/include/catalog/pg_proc.h,v
retrieving revision 1.313
diff -c -r1.313 pg_proc.h
*** src/include/catalog/pg_proc.h    17 Aug 2003 19:58:06 -0000    1.313
--- src/include/catalog/pg_proc.h    21 Oct 2003 05:36:56 -0000
***************
*** 1477,1483 ****
  DESCR("convert abstime to timestamp with time zone");
  DATA(insert OID = 1174 (  timestamptz       PGNSP PGUID 12 f f t f s 1 1184 "1082"  date_timestamptz - _null_ ));
  DESCR("convert date to timestamp with time zone");
! DATA(insert OID = 1176 (  timestamptz       PGNSP PGUID 14 f f t f s 2 1184 "1082 1083"    "select timestamptz($1 +
$2)"- _null_ )); 
  DESCR("convert date and time to timestamp with time zone");
  DATA(insert OID = 1177 (  interval           PGNSP PGUID 12 f f t f i 1 1186 "703"    reltime_interval - _null_ ));
  DESCR("convert reltime to interval");
--- 1477,1483 ----
  DESCR("convert abstime to timestamp with time zone");
  DATA(insert OID = 1174 (  timestamptz       PGNSP PGUID 12 f f t f s 1 1184 "1082"  date_timestamptz - _null_ ));
  DESCR("convert date to timestamp with time zone");
! DATA(insert OID = 1176 (  timestamptz       PGNSP PGUID 14 f f t f s 2 1184 "1082 1083"    "select
pg_catalog.timestamptz($1+ $2)" - _null_ )); 
  DESCR("convert date and time to timestamp with time zone");
  DATA(insert OID = 1177 (  interval           PGNSP PGUID 12 f f t f i 1 1186 "703"    reltime_interval - _null_ ));
  DESCR("convert reltime to interval");
***************
*** 1520,1528 ****
  DATA(insert OID = 1200 (  interval            PGNSP PGUID 12 f f t f i 2 1186 "1186 23"    interval_scale - _null_
));
  DESCR("adjust interval precision");

! DATA(insert OID = 1215 (  obj_description    PGNSP PGUID 14 f f t f s 2    25 "26 19"    "select description from
pg_descriptionwhere objoid = $1 and classoid = (select oid from pg_class where relname = $2 and relnamespace = PGNSP)
andobjsubid = 0" - _null_ )); 
  DESCR("get description for object id and catalog name");
! DATA(insert OID = 1216 (  col_description    PGNSP PGUID 14 f f t f s 2    25 "26 23"    "select description from
pg_descriptionwhere objoid = $1 and classoid = \'pg_catalog.pg_class\'::regclass and objsubid = $2" - _null_ )); 
  DESCR("get description for table column");

  DATA(insert OID = 1217 (  date_trunc       PGNSP PGUID 12 f f t f i 2 1184 "25 1184"    timestamptz_trunc - _null_
));
--- 1520,1528 ----
  DATA(insert OID = 1200 (  interval            PGNSP PGUID 12 f f t f i 2 1186 "1186 23"    interval_scale - _null_
));
  DESCR("adjust interval precision");

! DATA(insert OID = 1215 (  obj_description    PGNSP PGUID 14 f f t f s 2    25 "26 19"    "select description from
pg_catalog.pg_descriptionwhere objoid = $1 and classoid = (select oid from pg_catalog.pg_class where relname = $2 and
relnamespace= PGNSP) and objsubid = 0" - _null_ )); 
  DESCR("get description for object id and catalog name");
! DATA(insert OID = 1216 (  col_description    PGNSP PGUID 14 f f t f s 2    25 "26 23"    "select description from
pg_catalog.pg_descriptionwhere objoid = $1 and classoid = \'pg_catalog.pg_class\'::regclass and objsubid = $2" - _null_
));
  DESCR("get description for table column");

  DATA(insert OID = 1217 (  date_trunc       PGNSP PGUID 12 f f t f i 2 1184 "25 1184"    timestamptz_trunc - _null_
));
***************
*** 1683,1689 ****
   * This form of obj_description is now deprecated, since it will fail if
   * OIDs are not unique across system catalogs.    Use the other forms instead.
   */
! DATA(insert OID = 1348 (  obj_description     PGNSP PGUID 14 f f t f s 1 25 "26"  "select description from
pg_descriptionwhere objoid = $1 and objsubid = 0" - _null_ )); 
  DESCR("get description for object id (deprecated)");
  DATA(insert OID = 1349 (  oidvectortypes     PGNSP PGUID 12 f f t f s 1 25 "30"  oidvectortypes - _null_ ));
  DESCR("print type names of oidvector field");
--- 1683,1689 ----
   * This form of obj_description is now deprecated, since it will fail if
   * OIDs are not unique across system catalogs.    Use the other forms instead.
   */
! DATA(insert OID = 1348 (  obj_description     PGNSP PGUID 14 f f t f s 1 25 "26"  "select description from
pg_catalog.pg_descriptionwhere objoid = $1 and objsubid = 0" - _null_ )); 
  DESCR("get description for object id (deprecated)");
  DATA(insert OID = 1349 (  oidvectortypes     PGNSP PGUID 12 f f t f s 1 25 "30"  oidvectortypes - _null_ ));
  DESCR("print type names of oidvector field");
***************
*** 1740,1754 ****
  DATA(insert OID = 1381 (  char_length       PGNSP PGUID 12 f f t f i 1 23 "25"  textlen - _null_ ));
  DESCR("character length");

! DATA(insert OID = 1382 (  date_part    PGNSP PGUID 14 f f t f s 2  701 "25 702"  "select date_part($1,
timestamptz($2))"- _null_ )); 
  DESCR("extract field from abstime");
! DATA(insert OID = 1383 (  date_part    PGNSP PGUID 14 f f t f s 2  701 "25 703"  "select date_part($1, cast($2 as
interval))"- _null_ )); 
  DESCR("extract field from reltime");
! DATA(insert OID = 1384 (  date_part    PGNSP PGUID 14 f f t f i 2  701 "25 1082"    "select date_part($1, cast($2 as
timestampwithout time zone))" - _null_ )); 
  DESCR("extract field from date");
  DATA(insert OID = 1385 (  date_part    PGNSP PGUID 12 f f t f i 2  701 "25 1083"  time_part - _null_ ));
  DESCR("extract field from time");
! DATA(insert OID = 1386 (  age           PGNSP PGUID 14 f f t f s 1 1186 "1184"  "select age(cast(current_date as
timestampwith time zone), $1)" - _null_ )); 
  DESCR("date difference from today preserving months and years");

  DATA(insert OID = 1388 (  timetz       PGNSP PGUID 12 f f t f s 1 1266 "1184"  timestamptz_timetz - _null_ ));
--- 1740,1754 ----
  DATA(insert OID = 1381 (  char_length       PGNSP PGUID 12 f f t f i 1 23 "25"  textlen - _null_ ));
  DESCR("character length");

! DATA(insert OID = 1382 (  date_part    PGNSP PGUID 14 f f t f s 2  701 "25 702"  "select pg_catalog.date_part($1,
pg_catalog.timestamptz($2))"- _null_ )); 
  DESCR("extract field from abstime");
! DATA(insert OID = 1383 (  date_part    PGNSP PGUID 14 f f t f s 2  701 "25 703"  "select pg_catalog.date_part($1,
cast($2as pg_catalog.interval))" - _null_ )); 
  DESCR("extract field from reltime");
! DATA(insert OID = 1384 (  date_part    PGNSP PGUID 14 f f t f i 2  701 "25 1082"    "select pg_catalog.date_part($1,
cast($2as timestamp without time zone))" - _null_ )); 
  DESCR("extract field from date");
  DATA(insert OID = 1385 (  date_part    PGNSP PGUID 12 f f t f i 2  701 "25 1083"  time_part - _null_ ));
  DESCR("extract field from time");
! DATA(insert OID = 1386 (  age           PGNSP PGUID 14 f f t f s 1 1186 "1184"  "select
pg_catalog.age(cast(current_dateas timestamp with time zone), $1)" - _null_ )); 
  DESCR("date difference from today preserving months and years");

  DATA(insert OID = 1388 (  timetz       PGNSP PGUID 12 f f t f s 1 1266 "1184"  timestamptz_timetz - _null_ ));
***************
*** 1789,1797 ****
  DATA(insert OID = 1403 (  current_schemas    PGNSP PGUID 12 f f t f s 1    1003 "16"    current_schemas - _null_ ));
  DESCR("current schema search list");

! DATA(insert OID = 1404 (  overlay            PGNSP PGUID 14 f f t f i 4 25 "25 25 23 23"  "select substring($1, 1,
($3- 1)) || $2 || substring($1, ($3 + $4))" - _null_ )); 
  DESCR("substitute portion of string");
! DATA(insert OID = 1405 (  overlay            PGNSP PGUID 14 f f t f i 3 25 "25 25 23"  "select substring($1, 1, ($3 -
1))|| $2 || substring($1, ($3 + char_length($2)))" - _null_ )); 
  DESCR("substitute portion of string");

  DATA(insert OID = 1406 (  isvertical        PGNSP PGUID 12 f f t f i 2    16 "600 600"  point_vert - _null_ ));
--- 1789,1797 ----
  DATA(insert OID = 1403 (  current_schemas    PGNSP PGUID 12 f f t f s 1    1003 "16"    current_schemas - _null_ ));
  DESCR("current schema search list");

! DATA(insert OID = 1404 (  overlay            PGNSP PGUID 14 f f t f i 4 25 "25 25 23 23"  "select
pg_catalog.substring($1,1, ($3 - 1)) || $2 || pg_catalog.substring($1, ($3 + $4))" - _null_ )); 
  DESCR("substitute portion of string");
! DATA(insert OID = 1405 (  overlay            PGNSP PGUID 14 f f t f i 3 25 "25 25 23"  "select
pg_catalog.substring($1,1, ($3 - 1)) || $2 || pg_catalog.substring($1, ($3 + char_length($2)))" - _null_ )); 
  DESCR("substitute portion of string");

  DATA(insert OID = 1406 (  isvertical        PGNSP PGUID 12 f f t f i 2    16 "600 600"  point_vert - _null_ ));
***************
*** 1835,1841 ****
  DESCR("multiply box by point (scale)");
  DATA(insert OID = 1425 (  box_div            PGNSP PGUID 12 f f t f i 2 603 "603 600"    box_div - _null_ ));
  DESCR("divide box by point (scale)");
! DATA(insert OID = 1426 (  path_contain_pt    PGNSP PGUID 14 f f t f i 2    16 "602 600"  "select on_ppath($2, $1)" -
_null_)); 
  DESCR("path contains point?");
  DATA(insert OID = 1428 (  poly_contain_pt    PGNSP PGUID 12 f f t f i 2    16 "604 600"  poly_contain_pt - _null_ ));
  DESCR("polygon contains point?");
--- 1835,1841 ----
  DESCR("multiply box by point (scale)");
  DATA(insert OID = 1425 (  box_div            PGNSP PGUID 12 f f t f i 2 603 "603 600"    box_div - _null_ ));
  DESCR("divide box by point (scale)");
! DATA(insert OID = 1426 (  path_contain_pt    PGNSP PGUID 14 f f t f i 2    16 "602 600"  "select
pg_catalog.on_ppath($2,$1)" - _null_ )); 
  DESCR("path contains point?");
  DATA(insert OID = 1428 (  poly_contain_pt    PGNSP PGUID 12 f f t f i 2    16 "604 600"  poly_contain_pt - _null_ ));
  DESCR("polygon contains point?");
***************
*** 2015,2021 ****
  DESCR("center of");
  DATA(insert OID = 1543 (  center            PGNSP PGUID 12 f f t f i 1 600 "718"    circle_center - _null_ ));
  DESCR("center of");
! DATA(insert OID = 1544 (  polygon            PGNSP PGUID 14 f f t f i 1 604 "718"    "select polygon(12, $1)" -
_null_)); 
  DESCR("convert circle to 12-vertex polygon");
  DATA(insert OID = 1545 (  npoints            PGNSP PGUID 12 f f t f i 1    23 "602"  path_npoints - _null_ ));
  DESCR("number of points in path");
--- 2015,2021 ----
  DESCR("center of");
  DATA(insert OID = 1543 (  center            PGNSP PGUID 12 f f t f i 1 600 "718"    circle_center - _null_ ));
  DESCR("center of");
! DATA(insert OID = 1544 (  polygon            PGNSP PGUID 14 f f t f i 1 604 "718"    "select pg_catalog.polygon(12,
$1)"- _null_ )); 
  DESCR("convert circle to 12-vertex polygon");
  DATA(insert OID = 1545 (  npoints            PGNSP PGUID 12 f f t f i 1    23 "602"  path_npoints - _null_ ));
  DESCR("number of points in path");
***************
*** 2165,2173 ****
  DESCR("return portion of string");
  DATA(insert OID =  878 (  translate    PGNSP PGUID 12 f f t f i 3 25 "25 25 25"  translate - _null_ ));
  DESCR("map a set of character appearing in string");
! DATA(insert OID =  879 (  lpad           PGNSP PGUID 14 f f t f i 2 25 "25 23"    "select lpad($1, $2, \' \')" -
_null_)); 
  DESCR("left-pad string to length");
! DATA(insert OID =  880 (  rpad           PGNSP PGUID 14 f f t f i 2 25 "25 23"    "select rpad($1, $2, \' \')" -
_null_)); 
  DESCR("right-pad string to length");
  DATA(insert OID =  881 (  ltrim           PGNSP PGUID 12 f f t f i 1 25 "25"  ltrim1 - _null_ ));
  DESCR("trim spaces from left end of string");
--- 2165,2173 ----
  DESCR("return portion of string");
  DATA(insert OID =  878 (  translate    PGNSP PGUID 12 f f t f i 3 25 "25 25 25"  translate - _null_ ));
  DESCR("map a set of character appearing in string");
! DATA(insert OID =  879 (  lpad           PGNSP PGUID 14 f f t f i 2 25 "25 23"    "select pg_catalog.lpad($1, $2, \'
\')"- _null_ )); 
  DESCR("left-pad string to length");
! DATA(insert OID =  880 (  rpad           PGNSP PGUID 14 f f t f i 2 25 "25 23"    "select pg_catalog.rpad($1, $2, \'
\')"- _null_ )); 
  DESCR("right-pad string to length");
  DATA(insert OID =  881 (  ltrim           PGNSP PGUID 12 f f t f i 1 25 "25"  ltrim1 - _null_ ));
  DESCR("trim spaces from left end of string");
***************
*** 2315,2321 ****

  DATA(insert OID = 1698 (  position           PGNSP PGUID 12 f f t f i 2 23 "1560 1560" bitposition - _null_ ));
  DESCR("return position of sub-bitstring");
! DATA(insert OID = 1699 (  substring            PGNSP PGUID 14 f f t f i 2 1560 "1560 23"  "select substring($1, $2,
-1)"- _null_ )); 
  DESCR("return portion of bitstring");


--- 2315,2321 ----

  DATA(insert OID = 1698 (  position           PGNSP PGUID 12 f f t f i 2 23 "1560 1560" bitposition - _null_ ));
  DESCR("return position of sub-bitstring");
! DATA(insert OID = 1699 (  substring            PGNSP PGUID 14 f f t f i 2 1560 "1560 23"  "select
pg_catalog.substring($1,$2, -1)" - _null_ )); 
  DESCR("return portion of bitstring");


***************
*** 2445,2455 ****
  DESCR("sign of value");
  DATA(insert OID = 1707 ( round                    PGNSP PGUID 12 f f t f i 2 1700 "1700 23"  numeric_round - _null_
));
  DESCR("value rounded to 'scale'");
! DATA(insert OID = 1708 ( round                    PGNSP PGUID 14 f f t f i 1 1700 "1700"    "select round($1,0)" -
_null_)); 
  DESCR("value rounded to 'scale' of zero");
  DATA(insert OID = 1709 ( trunc                    PGNSP PGUID 12 f f t f i 2 1700 "1700 23"  numeric_trunc - _null_
));
  DESCR("value truncated to 'scale'");
! DATA(insert OID = 1710 ( trunc                    PGNSP PGUID 14 f f t f i 1 1700 "1700"    "select trunc($1,0)" -
_null_)); 
  DESCR("value truncated to 'scale' of zero");
  DATA(insert OID = 1711 ( ceil                    PGNSP PGUID 12 f f t f i 1 1700 "1700"    numeric_ceil - _null_ ));
  DESCR("smallest integer >= value");
--- 2445,2455 ----
  DESCR("sign of value");
  DATA(insert OID = 1707 ( round                    PGNSP PGUID 12 f f t f i 2 1700 "1700 23"  numeric_round - _null_
));
  DESCR("value rounded to 'scale'");
! DATA(insert OID = 1708 ( round                    PGNSP PGUID 14 f f t f i 1 1700 "1700"    "select
pg_catalog.round($1,0)"- _null_ )); 
  DESCR("value rounded to 'scale' of zero");
  DATA(insert OID = 1709 ( trunc                    PGNSP PGUID 12 f f t f i 2 1700 "1700 23"  numeric_trunc - _null_
));
  DESCR("value truncated to 'scale'");
! DATA(insert OID = 1710 ( trunc                    PGNSP PGUID 14 f f t f i 1 1700 "1700"    "select
pg_catalog.trunc($1,0)"- _null_ )); 
  DESCR("value truncated to 'scale' of zero");
  DATA(insert OID = 1711 ( ceil                    PGNSP PGUID 12 f f t f i 1 1700 "1700"    numeric_ceil - _null_ ));
  DESCR("smallest integer >= value");
***************
*** 2501,2507 ****
  DESCR("m raised to the power of n");
  DATA(insert OID = 1740 ( numeric                PGNSP PGUID 12 f f t f i 1 1700 "23"    int4_numeric - _null_ ));
  DESCR("(internal)");
! DATA(insert OID = 1741 ( log                    PGNSP PGUID 14 f f t f i 1 1700 "1700"    "select log(10, $1)" -
_null_)); 
  DESCR("logarithm base 10 of n");
  DATA(insert OID = 1742 ( numeric                PGNSP PGUID 12 f f t f i 1 1700 "700"  float4_numeric - _null_ ));
  DESCR("(internal)");
--- 2501,2507 ----
  DESCR("m raised to the power of n");
  DATA(insert OID = 1740 ( numeric                PGNSP PGUID 12 f f t f i 1 1700 "23"    int4_numeric - _null_ ));
  DESCR("(internal)");
! DATA(insert OID = 1741 ( log                    PGNSP PGUID 14 f f t f i 1 1700 "1700"    "select pg_catalog.log(10,
$1)"- _null_ )); 
  DESCR("logarithm base 10 of n");
  DATA(insert OID = 1742 ( numeric                PGNSP PGUID 12 f f t f i 1 1700 "700"  float4_numeric - _null_ ));
  DESCR("(internal)");
***************
*** 2575,2585 ****
  DESCR("I/O");


! DATA(insert OID = 1810 (  bit_length       PGNSP PGUID 14 f f t f i 1 23 "17" "select octet_length($1) * 8" - _null_
));
  DESCR("length in bits");
! DATA(insert OID = 1811 (  bit_length       PGNSP PGUID 14 f f t f i 1 23 "25" "select octet_length($1) * 8" - _null_
));
  DESCR("length in bits");
! DATA(insert OID = 1812 (  bit_length       PGNSP PGUID 14 f f t f i 1 23 "1560" "select length($1)" - _null_ ));
  DESCR("length in bits");

  /* Selectivity estimators for LIKE and related operators */
--- 2575,2585 ----
  DESCR("I/O");


! DATA(insert OID = 1810 (  bit_length       PGNSP PGUID 14 f f t f i 1 23 "17" "select pg_catalog.octet_length($1) *
8"- _null_ )); 
  DESCR("length in bits");
! DATA(insert OID = 1811 (  bit_length       PGNSP PGUID 14 f f t f i 1 23 "25" "select pg_catalog.octet_length($1) *
8"- _null_ )); 
  DESCR("length in bits");
! DATA(insert OID = 1812 (  bit_length       PGNSP PGUID 14 f f t f i 1 23 "1560" "select pg_catalog.length($1)" -
_null_)); 
  DESCR("length in bits");

  /* Selectivity estimators for LIKE and related operators */
***************
*** 2936,2942 ****
  DESCR("greater-than");
  DATA(insert OID = 2058 (  age                PGNSP PGUID 12 f f t f i 2 1186 "1114 1114"  timestamp_age - _null_ ));
  DESCR("date difference preserving months and years");
! DATA(insert OID = 2059 (  age                PGNSP PGUID 14 f f t f s 1 1186 "1114"    "select age(cast(current_date
astimestamp without time zone), $1)" - _null_ )); 
  DESCR("date difference from today preserving months and years");

  DATA(insert OID = 2069 (  timezone            PGNSP PGUID 12 f f t f s 2 1184 "25 1114"  timestamp_zone - _null_ ));
--- 2936,2942 ----
  DESCR("greater-than");
  DATA(insert OID = 2058 (  age                PGNSP PGUID 12 f f t f i 2 1186 "1114 1114"  timestamp_age - _null_ ));
  DESCR("date difference preserving months and years");
! DATA(insert OID = 2059 (  age                PGNSP PGUID 14 f f t f s 1 1186 "1114"    "select
pg_catalog.age(cast(current_dateas timestamp without time zone), $1)" - _null_ )); 
  DESCR("date difference from today preserving months and years");

  DATA(insert OID = 2069 (  timezone            PGNSP PGUID 12 f f t f s 2 1184 "25 1114"  timestamp_zone - _null_ ));
***************
*** 2950,2956 ****

  DATA(insert OID = 2073 (  substring            PGNSP PGUID 12 f f t f i 2 25 "25 25"    textregexsubstr - _null_ ));
  DESCR("extracts text matching regular expression");
! DATA(insert OID = 2074 (  substring            PGNSP PGUID 14 f f t f i 3 25 "25 25 25"    "select substring($1,
similar_escape($2,$3))" - _null_ )); 
  DESCR("extracts text matching SQL99 regular expression");

  DATA(insert OID = 2075 (  bit                PGNSP PGUID 12 f f t f i 1 1560 "20"    bitfromint8 - _null_ ));
--- 2950,2956 ----

  DATA(insert OID = 2073 (  substring            PGNSP PGUID 12 f f t f i 2 25 "25 25"    textregexsubstr - _null_ ));
  DESCR("extracts text matching regular expression");
! DDATA(insert OID = 2074 (  substring            PGNSP PGUID 14 f f t f i 3 25 "25 25 25"    "select
pg_catalog.substring($1,pg_catalog.similar_escape($2, $3))" - _null_ )); 
  DESCR("extracts text matching SQL99 regular expression");

  DATA(insert OID = 2075 (  bit                PGNSP PGUID 12 f f t f i 1 1560 "20"    bitfromint8 - _null_ ));