Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA73376 for ; Tue, 24 Aug 1999 10:23:11 -0400 (EDT) (envelope-from jose@sferacarta.com) Received: from proxy.sferacarta.com (mail@sfcabop1.nettuno.it [193.207.10.213]) by bologna.nettuno.it (8.9.3/8.9.3/NETTuno 3.3) with ESMTP id QAA15137; Tue, 24 Aug 1999 16:21:10 +0200 (MDT) Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5] by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian)) id 11JIZ6-0002AK-00; Tue, 24 Aug 1999 15:32:48 +0000 Message-ID: <37C2A012.C400A443@sferacarta.com> Date: Tue, 24 Aug 1999 15:37:23 +0200 From: =?iso-8859-1?Q?Jos=E9?= Soares X-Mailer: Mozilla 4.5 [it] (Win95; I) X-Accept-Language: it MIME-Version: 1.0 To: Bruce Momjian CC: Scott Barron , Albert REINER , PostgreSQL-SQL Subject: Re: [SQL] Expression with aggregate References: <199908240424.AAA16390@candle.pha.pa.us> Content-Type: multipart/alternative; boundary="------------708D7F7253B0BC526D38D496" --------------708D7F7253B0BC526D38D496 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit In PostgreSQL a view is a table with a special rule named: "_RETnametable" where nametable stands for the table name used by the view. eg: CREATE RULE "_RETvista" AS ON SELECT TO "vista" DO INSTEAD SELECT "a" FROM "tabella"; Actually, psql checks in the field pg_class.relkind to see if it is a table or other kind of class ('i'=index, S=sequence, 'r'=table/view, etc). If psql finds a 'r' into pg_class.relkind then it checks in the boolean field pg_class.relhasrules and prints "table" if its value is FALSE or "view?" if its value is TRUE. The field pg_class.relhasrules is also used for every kind of rule in the table, this is the reason why psql prints "view?" because it doesn't know for sure if the rule is a "_RET..." (view) or other kind of rule. QUERY: SELECT usename, relname, relkind, relhasrules FROM pg_class, pg_user WHER E usesysid = relowner and ( relkind = 'r' OR relkind = 'i' OR relkind = 'S') and relname !~ '^pg_' and (relkind != 'i' OR relname !~ '^xinx') ORDER BY relname Database = prova +------------------+----------------------------------+----------+ | Owner | Relation | Type | +------------------+----------------------------------+----------+ | postgres | serial | sequence | | postgres | t_a_seq | sequence | | postgres | tabella | table | | postgres | vista | view? | +------------------+----------------------------------+----------+ There's an exactly way to know if the table is a view or a table; the function pg_get_viewdef() But I don't know why psql doesn't use it... prova=> select pg_get_viewdef('vista'); pg_get_viewdef -------------------------- SELECT "a" FROM "tabella"; (1 row) prova=> select pg_get_viewdef('tabella'); pg_get_viewdef -------------- Not a view (1 row) José Bruce Momjian ha scritto: > > > > That should provide the desired results. As for the > > question about the view in the \d output, I don't know > > why its like that, I've wondered myself. > > I think it is view? because it could be a view, or a rule? > > > > > > > By the way, when I use \d to list the tables, why is > > > a view always > > > shown with a '?' like in > > > > > > asdf=> \d > > > Database = asdf > > > > > > > > +------------------+----------------------------------+----------+ > > > | Owner | Relation > > > | Type | > > > > > > > > +------------------+----------------------------------+----------+ > > > | albert | a > > > | table | > > > | albert | b > > > | table | > > > | albert | c > > > | view? | > > > > > > > > +------------------+----------------------------------+----------+ > > > > > > ? Why doesn't it just say '| albert | c | view |'? > > > > > > I'd really appreciate any hints with this, even > > > though I know how to > > > do it (as demonstrated above) with the use of the > > > view. > > > > > > Thanks in advance for your help, > > > > > > Albert. > > > > > > -- > > > > > > > > --------------------------------------------------------------------------- > > > Post an / Mail to / Skribu al: Albert Reiner > > > > > > > > --------------------------------------------------------------------------- > > > > > > ************ > > > > > > > > > > __________________________________________________ > > Do You Yahoo!? > > Bid and sell for free at http://auctions.yahoo.com > > > > > > ************ > > > > > > -- > Bruce Momjian | http://www.op.net/~candle > maillist@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ************ --------------708D7F7253B0BC526D38D496 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit In PostgreSQL a view is a table with a special rule named: "_RETnametable" where nametable stands for the table name used by the view.

eg:
CREATE RULE "_RETvista" AS ON SELECT TO "vista" DO INSTEAD SELECT "a" FROM "tabella";

Actually, psql checks in the field pg_class.relkind to see if it is a table or other kind of class ('i'=index, S=sequence, 'r'=table/view, etc).
If psql finds a 'r' into pg_class.relkind then it checks in the boolean field pg_class.relhasrules and prints "table" if its value is FALSE or "view?" if its value is TRUE.
The field pg_class.relhasrules is also used for every kind of rule in the table,
this is the reason why psql prints "view?" because it doesn't know for sure if the rule is
a "_RET..." (view) or other kind of rule.
 

QUERY: SELECT usename, relname, relkind, relhasrules FROM pg_class, pg_user WHER
E usesysid = relowner and ( relkind = 'r' OR relkind = 'i' OR relkind = 'S') and
 relname !~ '^pg_' and (relkind != 'i' OR relname !~ '^xinx')  ORDER BY relname

Database    = prova
 +------------------+----------------------------------+----------+
 |  Owner           |             Relation             |   Type   |
 +------------------+----------------------------------+----------+
 | postgres         | serial                           | sequence |
 | postgres         | t_a_seq                          | sequence |
 | postgres         | tabella                          | table    |
 | postgres         | vista                            | view?    |
 +------------------+----------------------------------+----------+

There's an exactly way to know if the table is a view or a table; the function pg_get_viewdef()
But I don't know why psql doesn't use it...
 

prova=> select pg_get_viewdef('vista');
pg_get_viewdef
--------------------------
SELECT "a" FROM "tabella";
(1 row)
 

prova=> select pg_get_viewdef('tabella');
pg_get_viewdef
--------------
Not a view
(1 row)
 
 

José
 
 

Bruce Momjian ha scritto:

>
> That should provide the desired results.  As for the
> question about the view in the \d output, I don't know
> why its like that, I've wondered myself.

I think it is view? because it could be a view, or a rule?

> >
> > By the way, when I use \d to list the tables, why is
> > a view always
> > shown with a '?' like in
> >
> > asdf=> \d
> > Database    = asdf
> >
> >
> +------------------+----------------------------------+----------+
> >  |  Owner           |             Relation
> >   |   Type   |
> >
> >
> +------------------+----------------------------------+----------+
> >  | albert           | a
> >   | table    |
> >  | albert           | b
> >   | table    |
> >  | albert           | c
> >   | view?    |
> >
> >
> +------------------+----------------------------------+----------+
> >
> > ? Why doesn't it just say '| albert | c | view |'?
> >
> > I'd really appreciate any hints with this, even
> > though I know how to
> > do it (as demonstrated above) with the use of the
> > view.
> >
> > Thanks in advance for your help,
> >
> > Albert.
> >
> > --
> >
> >
> ---------------------------------------------------------------------------
> >   Post an / Mail to / Skribu al: Albert Reiner
> > <areiner@tph.tuwien.ac.at>
> >
> ---------------------------------------------------------------------------
> >
> > ************
> >
> >
>
> __________________________________________________
> Do You Yahoo!?
> Bid and sell for free at http://auctions.yahoo.com
>
>
> ************
>
>

--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

************

--------------708D7F7253B0BC526D38D496--