Re: [SQL] Expression with aggregate - Mailing list pgsql-sql

<tt>In PostgreSQL a view is a table with a special rule named: "_RETnametable" where nametable stands for the table
nameused by the view.</tt><tt></tt><p><tt>eg:</tt><br /><tt>CREATE RULE "_RETvista" AS ON SELECT TO "vista" DO INSTEAD
SELECT"a" FROM "tabella";</tt><tt></tt><p><tt>Actually, psql checks in the field pg_class.relkind to see if it is a
tableor other kind of class ('i'=index, S=sequence, 'r'=table/view, etc).</tt><br /><tt>If psql finds a 'r' into
pg_class.relkindthen it checks in the boolean field pg_class.relhasrules and prints "table" if its value is FALSE or
"view?"if its value is TRUE.</tt><br /><tt>The field pg_class.relhasrules is also used for every kind of rule in the
table,</tt><br/><tt>this is the reason why psql prints "view?" because it doesn't know for sure if the rule is</tt><br
/><tt>a"_RET..." (view) or other kind of rule.</tt><br /><tt></tt> <tt></tt><p><tt>QUERY: SELECT usename, relname,
relkind,relhasrules FROM pg_class, pg_user WHER</tt><br /><tt>E usesysid = relowner and ( relkind = 'r' OR relkind =
'i'OR relkind = 'S') and</tt><br /><tt> relname !~ '^pg_' and (relkind != 'i' OR relname !~ '^xinx')  ORDER BY
relname</tt><tt></tt><p><tt>Database   = prova</tt><br
/><tt> +------------------+----------------------------------+----------+</tt><br/><tt> |  Owner          
|            Relation             |   Type   |</tt><br
/><tt> +------------------+----------------------------------+----------+</tt><br/><tt> | postgres         |
serial                          | sequence |</tt><br /><tt> | postgres         | t_a_seq                          |
sequence|</tt><br /><tt> | postgres         | tabella                          | table    |</tt><br /><tt> |
postgres        | vista                            | view?    |</tt><br
/><tt> +------------------+----------------------------------+----------+</tt><tt></tt><p><tt>There'san exactly way to
knowif the table is a view or a table; the function pg_get_viewdef()</tt><br /><tt>But I don't know why psql doesn't
useit...</tt><br /><tt></tt> <tt></tt><p><tt>prova=> select pg_get_viewdef('vista');</tt><br
/><tt>pg_get_viewdef</tt><br/><tt>--------------------------</tt><br /><tt>SELECT "a" FROM "tabella";</tt><br /><tt>(1
row)</tt><br/><tt></tt> <tt></tt><p><tt>prova=> select pg_get_viewdef('tabella');</tt><br
/><tt>pg_get_viewdef</tt><br/><tt>--------------</tt><br /><tt>Not a view</tt><br /><tt>(1 row)</tt><br /><tt></tt> 
<br/><tt></tt> <tt></tt><p><tt>José</tt><br /><tt></tt>  <br />  <p>Bruce Momjian ha scritto: <blockquote
type="CITE">><br />> That should provide the desired results.  As for the <br />> question about the view in
the\d output, I don't know <br />> why its like that, I've wondered myself. <p>I think it is view? because it could
bea view, or a rule? <p>> > <br />> > By the way, when I use \d to list the tables, why is <br />> >
aview always <br />> > shown with a '?' like in <br />> > <br />> > asdf=> \d <br />> >
Database   = asdf <br />> > <br />> > <br />>
+------------------+----------------------------------+----------+<br />> >  |  Owner           |            
Relation<br />> >   |   Type   | <br />> > <br />> > <br />>
+------------------+----------------------------------+----------+<br />> >  | albert           | a <br />>
>  | table    | <br />> >  | albert           | b <br />> >   | table    | <br />> >  |
albert          | c <br />> >   | view?    | <br />> > <br />> > <br />>
+------------------+----------------------------------+----------+<br />> > <br />> > ? Why doesn't it just
say'| albert | c | view |'? <br />> > <br />> > I'd really appreciate any hints with this, even <br />>
>though I know how to <br />> > do it (as demonstrated above) with the use of the <br />> > view. <br
/>>> <br />> > Thanks in advance for your help, <br />> > <br />> > Albert. <br />> > <br
/>>> -- <br />> > <br />> > <br />>
---------------------------------------------------------------------------<br />> >   Post an / Mail to / Skribu
al:Albert Reiner <br />> > <areiner@tph.tuwien.ac.at> <br />> > <br />>
---------------------------------------------------------------------------<br />> > <br />> > ************
<br/>> > <br />> > <br />> <br />> __________________________________________________ <br />> Do
YouYahoo!? <br />> Bid and sell for free at <a href="http://auctions.yahoo.com">http://auctions.yahoo.com</a><br
/>><br />> <br />> ************ <br />> <br />> <p>-- <br />  Bruce Momjian                        |  <a
href="http://www.op.net/~candle">http://www.op.net/~candle</a><br/>  maillist@candle.pha.pa.us            |  (610)
853-3000<br />  +  If your life is a hard drive,     |  830 Blythe Avenue <br />  +  Christ can be your backup.       
| Drexel Hill, Pennsylvania 19026 <p>************</blockquote>