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_description where objoid = $1 and classoid =
(select oid from 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_description where 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_description where 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_description where 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_description where 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_description where 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 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 age(cast(current_date as 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_ ));
--- 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($2 as 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($2 as 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_date as 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 as 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_ ));
--- 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_date as 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_ ));
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
