Fix two bugs in funcs.source that made the tutorial script fail.
Make a LOT of fixes to syscat.source to:
* Set search_path properly (and reset it)
* Add schema name to all results
* Add schema name to ORDER BY first
* Make checks for user-defined objects match reality
* format_type all type names
* Respect attisdropped
* Change !~ to 'not like' since it's more standard
All changes have been tested.
Chris
? src/tutorial/.deps
? src/tutorial/advanced.sql
? src/tutorial/basics.sql
? src/tutorial/complex.sql
? src/tutorial/funcs.sql
? src/tutorial/syscat.sql
Index: src/tutorial/funcs.source
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/tutorial/funcs.source,v
retrieving revision 1.5
diff -c -r1.5 funcs.source
*** src/tutorial/funcs.source 26 Oct 2001 20:45:33 -0000 1.5
--- src/tutorial/funcs.source 23 Oct 2003 14:25:00 -0000
***************
*** 153,162 ****
DROP FUNCTION copytext(text);
DROP FUNCTION makepoint(point,point);
DROP FUNCTION add_one(int4);
! DROP FUNCTION clean_EMP();
DROP FUNCTION high_pay();
DROP FUNCTION new_emp();
DROP FUNCTION add_em(int4, int4);
DROP FUNCTION one();
DROP TABLE EMP;
--- 153,163 ----
DROP FUNCTION copytext(text);
DROP FUNCTION makepoint(point,point);
DROP FUNCTION add_one(int4);
! --DROP FUNCTION clean_EMP();
DROP FUNCTION high_pay();
DROP FUNCTION new_emp();
DROP FUNCTION add_em(int4, int4);
DROP FUNCTION one();
+ DROP FUNCTION double_salary(EMP);
DROP TABLE EMP;
Index: src/tutorial/syscat.source
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/tutorial/syscat.source,v
retrieving revision 1.9
diff -c -r1.9 syscat.source
*** src/tutorial/syscat.source 4 Aug 2003 23:59:41 -0000 1.9
--- src/tutorial/syscat.source 23 Oct 2003 14:25:01 -0000
***************
*** 12,17 ****
--- 12,23 ----
---------------------------------------------------------------------------
--
+ -- Sets the schema search path to pg_catalog first, so that we do not
+ -- need to qualify every system object
+ --
+ SET SEARCH_PATH TO pg_catalog;
+
+ --
-- lists the name of all database adminstrators and the name of their
-- database(s)
--
***************
*** 23,139 ****
--
-- lists all user-defined classes
--
! SELECT relname
! FROM pg_class
! WHERE relkind = 'r' -- not indices, views, etc
! and relname !~ '^pg_' -- not catalogs
! ORDER BY relname;
--
-- lists all simple indices (ie. those that are defined over one simple
-- column reference)
--
! SELECT bc.relname AS class_name,
ic.relname AS index_name,
a.attname
! FROM pg_class bc, -- base class
pg_class ic, -- index class
pg_index i,
pg_attribute a -- att in base
! WHERE i.indrelid = bc.oid
and i.indexrelid = ic.oid
and i.indkey[0] = a.attnum
and i.indnatts = 1
and a.attrelid = bc.oid
! ORDER BY class_name, index_name, attname;
--
-- lists the user-defined attributes and their types for all user-defined
-- classes
--
! SELECT c.relname, a.attname, t.typname
! FROM pg_class c, pg_attribute a, pg_type t
! WHERE c.relkind = 'r' -- no indices
! and c.relname !~ '^pg_' -- no catalogs
and a.attnum > 0 -- no system att's
and a.attrelid = c.oid
and a.atttypid = t.oid
! ORDER BY relname, attname;
--
-- lists all user-defined base types (not including array types)
--
! SELECT u.usename, t.typname
! FROM pg_type t, pg_user u
WHERE u.usesysid = t.typowner
and t.typrelid = '0'::oid -- no complex types
and t.typelem = '0'::oid -- no arrays
! and u.usename <> 'postgres'
! ORDER BY usename, typname;
--
-- lists all left unary operators
--
! SELECT o.oprname AS left_unary,
! right_type.typname AS operand,
! result.typname AS return_type
! FROM pg_operator o, pg_type right_type, pg_type result
! WHERE o.oprkind = 'l' -- left unary
and o.oprright = right_type.oid
and o.oprresult = result.oid
! ORDER BY operand;
--
-- lists all right unary operators
--
! SELECT o.oprname AS right_unary,
! left_type.typname AS operand,
! result.typname AS return_type
! FROM pg_operator o, pg_type left_type, pg_type result
! WHERE o.oprkind = 'r' -- right unary
and o.oprleft = left_type.oid
and o.oprresult = result.oid
! ORDER BY operand;
--
-- lists all binary operators
--
! SELECT o.oprname AS binary_op,
! left_type.typname AS left_opr,
! right_type.typname AS right_opr,
! result.typname AS return_type
! FROM pg_operator o, pg_type left_type, pg_type right_type, pg_type result
! WHERE o.oprkind = 'b' -- binary
and o.oprleft = left_type.oid
and o.oprright = right_type.oid
and o.oprresult = result.oid
! ORDER BY left_opr, right_opr;
--
-- lists the name, number of arguments and the return type of all user-defined
-- C functions
--
! SELECT p.proname, p.pronargs, t.typname
! FROM pg_proc p, pg_language l, pg_type t
! WHERE p.prolang = l.oid
and p.prorettype = t.oid
and l.lanname = 'c'
! ORDER BY proname;
--
-- lists all aggregate functions and the types to which they can be applied
--
! SELECT p.proname, t.typname
! FROM pg_aggregate a, pg_proc p, pg_type t
! WHERE a.aggfnoid = p.oid
and p.proargtypes[0] = t.oid
! ORDER BY proname, typname;
--
--- 29,168 ----
--
-- lists all user-defined classes
--
! SELECT pgn.nspname, pgc.relname
! FROM pg_class pgc, pg_namespace pgn
! WHERE pgc.relnamespace=pgn.oid
! and pgc.relkind = 'r' -- not indices, views, etc
! and pgn.nspname not like 'pg_%' -- not catalogs
! and pgn.nspname != 'information_schema' -- not information_schema
! ORDER BY nspname, relname;
--
-- lists all simple indices (ie. those that are defined over one simple
-- column reference)
--
! SELECT n.nspname AS schema_name,
! bc.relname AS class_name,
ic.relname AS index_name,
a.attname
! FROM pg_namespace n,
! pg_class bc, -- base class
pg_class ic, -- index class
pg_index i,
pg_attribute a -- att in base
! WHERE bc.relnamespace = n.oid
! and i.indrelid = bc.oid
and i.indexrelid = ic.oid
and i.indkey[0] = a.attnum
and i.indnatts = 1
and a.attrelid = bc.oid
! ORDER BY schema_name, class_name, index_name, attname;
--
-- lists the user-defined attributes and their types for all user-defined
-- classes
--
! SELECT n.nspname, c.relname, a.attname, format_type(t.oid, null) as typname
! FROM pg_namespace n, pg_class c,
! pg_attribute a, pg_type t
! WHERE n.oid = c.relnamespace
! and c.relkind = 'r' -- no indices
! and n.nspname not like 'pg_%' -- no catalogs
! and n.nspname != 'information_schema' -- no information_schema
and a.attnum > 0 -- no system att's
+ and not a.attisdropped -- no dropped columns
and a.attrelid = c.oid
and a.atttypid = t.oid
! ORDER BY nspname, relname, attname;
--
-- lists all user-defined base types (not including array types)
--
! SELECT n.nspname, u.usename, format_type(t.oid, null) as typname
! FROM pg_type t, pg_user u, pg_namespace n
WHERE u.usesysid = t.typowner
+ and t.typnamespace = n.oid
and t.typrelid = '0'::oid -- no complex types
and t.typelem = '0'::oid -- no arrays
! and n.nspname not like 'pg_%' -- no catalogs
! and n.nspname != 'information_schema' -- no information_schema
! ORDER BY nspname, usename, typname;
--
-- lists all left unary operators
--
! SELECT n.nspname, o.oprname AS left_unary,
! format_type(right_type.oid, null) AS operand,
! format_type(result.oid, null) AS return_type
! FROM pg_namespace n, pg_operator o,
! pg_type right_type, pg_type result
! WHERE o.oprnamespace = n.oid
! and o.oprkind = 'l' -- left unary
and o.oprright = right_type.oid
and o.oprresult = result.oid
! ORDER BY nspname, operand;
--
-- lists all right unary operators
--
! SELECT n.nspname, o.oprname AS right_unary,
! format_type(left_type.oid, null) AS operand,
! format_type(result.oid, null) AS return_type
! FROM pg_namespace n, pg_operator o,
! pg_type left_type, pg_type result
! WHERE o.oprnamespace = n.oid
! and o.oprkind = 'r' -- right unary
and o.oprleft = left_type.oid
and o.oprresult = result.oid
! ORDER BY nspname, operand;
--
-- lists all binary operators
--
! SELECT n.nspname, o.oprname AS binary_op,
! format_type(left_type.oid, null) AS left_opr,
! format_type(right_type.oid, null) AS right_opr,
! format_type(result.oid, null) AS return_type
! FROM pg_namespace n, pg_operator o, pg_type left_type,
! pg_type right_type, pg_type result
! WHERE o.oprnamespace = n.oid
! and o.oprkind = 'b' -- binary
and o.oprleft = left_type.oid
and o.oprright = right_type.oid
and o.oprresult = result.oid
! ORDER BY nspname, left_opr, right_opr;
--
-- lists the name, number of arguments and the return type of all user-defined
-- C functions
--
! SELECT n.nspname, p.proname, p.pronargs, format_type(t.oid, null) as return_type
! FROM pg_namespace n, pg_proc p,
! pg_language l, pg_type t
! WHERE p.pronamespace = n.oid
! and n.nspname not like 'pg_%' -- no catalogs
! and n.nspname != 'information_schema' -- no information_schema
! and p.prolang = l.oid
and p.prorettype = t.oid
and l.lanname = 'c'
! ORDER BY nspname, proname, pronargs, return_type;
--
-- lists all aggregate functions and the types to which they can be applied
--
! SELECT n.nspname, p.proname, format_type(t.oid, null) as typname
! FROM pg_namespace n, pg_aggregate a,
! pg_proc p, pg_type t
! WHERE p.pronamespace = n.oid
! and a.aggfnoid = p.oid
and p.proargtypes[0] = t.oid
! ORDER BY nspname, proname, typname;
--
***************
*** 141,149 ****
-- as well as the operators that cn be used with the respective operator
-- classes
--
! SELECT am.amname, opc.opcname, opr.oprname
! FROM pg_am am, pg_opclass opc, pg_amop amop, pg_operator opr
! WHERE opc.opcamid = am.oid
and amop.amopclaid = opc.oid
and amop.amopopr = opr.oid
! ORDER BY amname, opcname, oprname;
--- 170,186 ----
-- as well as the operators that cn be used with the respective operator
-- classes
--
! SELECT n.nspname, am.amname, opc.opcname, opr.oprname
! FROM pg_namespace n, pg_am am, pg_opclass opc,
! pg_amop amop, pg_operator opr
! WHERE opc.opcnamespace = n.oid
! and opc.opcamid = am.oid
and amop.amopclaid = opc.oid
and amop.amopopr = opr.oid
! ORDER BY nspname, amname, opcname, oprname;
!
! --
! -- Reset the search path
! --
! RESET SEARCH_PATH;
!
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
