Bug in the information_schema.referential_constraints view

If PostgreSQL failed to compile on your computer or you found a bug that
is likely to be specific to one platform then please fill out this form
and e-mail it to pgsql-ports(at)postgresql(dot)org(dot)

To report any other bug, fill out the form below and e-mail it to
pgsql-bugs(at)postgresql(dot)org(dot)

If you not only found the problem but solved it and generated a patch
then e-mail it to pgsql-patches(at)postgresql(dot)org instead. Please use the
command "diff -c" to generate the patch.

You may also enter a bug report at http://www.postgresql.org/ instead of
e-mail-ing this form.

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Vivien MALERBA
Your email address : malerba(at)gnome-db(dot)org

System Configuration
---------------------
Architecture (example: Intel Pentium) :Intel Pentium

Operating System (example: Linux 2.0.26 ELF) :Linux 2.4.7-10

PostgreSQL version (example: PostgreSQL-7.3): PostgreSQL-7.4 Beta 4

Compiler used (example: gcc 2.95.2) :gcc 3.0.2

Please enter a FULL description of your problem:
------------------------------------------------
The information_schema.referential_constraints retuns wrong data because
there is
an incomplete joining condition in the WHERE clause.

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
Fix the buggy joining condition in the view itself. Here is the working
view (probably
to be integrated into backend/catalog/information_schema.sql).

Sorry, I did not have the time to produce a patch...

CREATE VIEW referential_constraints AS
SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
CAST(con.conname AS sql_identifier) AS constraint_name,
CAST(current_database() AS sql_identifier) AS
unique_constraint_catalog,
CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,

CAST(
CASE con.confmatchtype WHEN 'f' THEN 'FULL'
WHEN 'p' THEN 'PARTIAL'
WHEN 'u' THEN 'NONE' END
AS character_data) AS match_option,

CAST(
CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
WHEN 'n' THEN 'SET NULL'
WHEN 'd' THEN 'SET DEFAULT'
WHEN 'r' THEN 'RESTRICT'
WHEN 'a' THEN 'NOACTION' END
AS character_data) AS update_rule,

CAST(
CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
WHEN 'n' THEN 'SET NULL'
WHEN 'd' THEN 'SET DEFAULT'
WHEN 'r' THEN 'RESTRICT'
WHEN 'a' THEN 'NOACTION' END
AS character_data) AS delete_rule

FROM pg_namespace ncon,
pg_constraint con,
pg_class c,
pg_constraint pkc,
pg_namespace npkc,
pg_user u

WHERE ncon.oid = con.connamespace
AND con.conrelid = c.oid
AND con.confkey = pkc.conkey
AND pkc.connamespace = npkc.oid
AND c.relowner = u.usesysid
AND c.relkind = 'r'
AND con.contype = 'f'
AND con.confrelid = pkc.conrelid
AND u.usename = current_user;