Hi guys,
Not sure if this is a known issue or not, but I think I may have found a bug with the way view definitions are shown... at least in psql.
Using 7.5 development CVS (as of a few hours ago) or even 7.4.3, if I connect using it's version of psql to a database (of the same version), then use psql to view the information_schema.constraint_columns_usage view, it gives me this definition:
***********
mydb=# \d information_schema.constraint_column_usage
View "information_schema.constraint_column_usage"
Column | Type | Modifiers
--------------------+-----------------------------------+-----------
table_catalog | information_schema.sql_identifier |
table_schema | information_schema.sql_identifier |
table_name | information_schema.sql_identifier |
column_name | information_schema.sql_identifier |
constraint_catalog | information_schema.sql_identifier |
constraint_schema | information_schema.sql_identifier |
constraint_name | information_schema.sql_identifier |
View definition:
SELECT current_database()::information_schema.sql_identifier AS table_catalog, x.tblschema::information_schema.sql_identifier AS table_schema, x.tblname::information_schema.sql_identifier AS table_name, x.colname::information_schema.sql_identifier AS column_name, current_database()::information_schema.sql_identifier AS constraint_catalog, x.cstrschema::information_schema.sql_identifier AS constraint_schema, x.cstrname::information_schema.sql_identifier AS constraint_name
FROM ( SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
WHERE nr.oid = r.relnamespace AND r.oid = a.attrelid AND d.refclassid = 'pg_class'::regclass::oid AND d.refobjid = r.oid AND d.refobjsubid = a.attnum AND d.classid = 'pg_constraint'::regclass::oid AND d.objid = c.oid AND c.connamespace = nc.oid AND c.contype = 'c'::"char" AND r.relkind = 'r'::"char" AND NOT a.attisdropped
ORDER BY nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
UNION ALL
SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, pg_constraint c, information_schema._pg_keypositions() pos(n)
WHERE nr.oid = r.relnamespace AND r.oid = a.attrelid AND nc.oid = c.connamespace AND
CASE
WHEN c.contype = 'f'::"char" THEN r.oid = c.confrelid AND c.confkey[pos.n] = a.attnum
ELSE r.oid = c.conrelid AND c.conkey[pos.n] = a.attnum
END AND NOT a.attisdropped AND (c.contype = 'p'::"char" OR c.contype = 'u'::"char" OR c.contype = 'f'::"char") AND r.relkind = 'r'::"char") x(tblschema, tblname, tblowner, colname, cstrschema, cstrname), pg_user u
WHERE x.tblowner = u.usesysid AND u.usename = "current_user"();
mydb=#
***********
However, when I use this definition (cut-n-paste style to avoid mistakes) to create the view anew (even with a different name, etc), then it gives me an error:
***********
mydb=# \e
ERROR: parse error at or near "ALL" at character 1105
ERROR: parse error at or near "ALL" at character 1105
LINE 6: UNION ALL
^
mydb=#***********
I haven't come across this before, and am having the same problem with pgAdmin3 as well, as it supplies the exact same definition of the view.
I think I'm doing everything right here, could this be a bug with PG?
Regards and best wishes,
Justin Clift
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
