Skip site navigation (1) Skip section navigation (2)

Bug with view definitions?

From: Justin Clift <jc(at)telstra(dot)net>
To: PostgreSQL Hackers Mailing List <pgsql-hackers(at)postgresql(dot)org>
Subject: Bug with view definitions?
Date: 2004-07-01 10:42:57
Message-ID: 40E3EAB1.70607@telstra.net (view raw or flat)
Thread:
Lists: pgsql-hackers
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


Responses

pgsql-hackers by date

Next:From: Dennis BjorklundDate: 2004-07-01 11:31:47
Subject: Re: Bug with view definitions?
Previous:From: Dave PageDate: 2004-07-01 07:30:28
Subject: Re: Adding column comment to information_schema.columns

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group