Hi,

Currently psql show views like:

Database    = hygea
 +------------------+----------------------------------+----------+
 |  Owner           |             Relation             |   Type   |
 +------------------+----------------------------------+----------+
 | postgres         | abbattimenti                     | table    |
 | postgres         | wattivita                        | view?    |
 | postgres         | attivita_a                       | table    |

because it seeks for relhasrules field and if you have a table (not a
table) with a rule it thinks it is a view
and displays "view?" instead of "table".

I modified psql.c to use pg_get_viewdef() function to seek for views and
now I can display only tables using \dt
or only views using \dv like:hygea=> \dv

\dv
Database    = hygea
 +------------------+----------------------------------+----------+
 |  Owner           |             Relation             |   Type   |
 +------------------+----------------------------------+----------+
 | postgres         | wattivita                        | view     |
 | postgres         | wtabelle                         | view     |
 +------------------+----------------------------------+----------+

\dt
Database    = hygea
 +------------------+----------------------------------+----------+
 |  Owner           |             Relation             |   Type   |
 +------------------+----------------------------------+----------+
 | postgres         | abbattimenti                     | table    |
 | postgres         | attivita                         | table    |
 | postgres         | attivita_a                       | table    |
 | postgres         | attivita_b                       | table    |
 | postgres         | brogliacci                       | table    |
 | postgres         | capi                             | table    |
 | postgres         | comuni                           | table    |
 +------------------+----------------------------------+----------+

If this interests to someone there is the attached patch.

-Jose'-
Fri, 22 Jan 1999 15:38:18 +0100
"Jose' Soares" <jose@sferacarta.com>
view?
Jose' Soares wrote:

> Hi,
>
> Currently psql show views like:
>
> Database    = hygea
>  +------------------+----------------------------------+----------+
>  |  Owner           |             Relation             |   Type   |
>  +------------------+----------------------------------+----------+
>  | postgres         | abbattimenti                     | table    |
>  | postgres         | wattivita                        | view?    |
>  | postgres         | attivita_a                       | table    |
>
> because it seeks for relhasrules field and if you have a table (not a
> table) with a rule it thinks it is a view
> and displays "view?" instead of "table".
>
> I modified psql.c to use pg_get_viewdef() function to seek for views and
> now I can display only tables using \dt
> or only views using \dv like:hygea=> \dv
> [...]

    I suggest not to apply this patch

    1.  The  function  pg_get_viewdef()  is  definitely  too much
        overhead. In fact it must parse back  the  complete  view
        definition, doing many system table lookups, just to tell
        if this is a view or not.

    2.  The function pg_get_viewdef() is currently  out  of  sync
        with  the possible parsetrees for rule actions. CASE (and
        maybe some other constructs) aren't implemented and if it
        hit's on such a rule it will elog() out.

    Rules  on  SELECT  event are restricted totally to view rules
    since v6.4.  There can be only one rule  on  SELECT  that  is
    INSTEAD and selects exactly the attributes on one table.  And
    AFAIC this restriction will stay.  The  check  should  be  if
    there is a rule with event SELECT --> view.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #



Fri, 22 Jan 1999 17:52:43 +0100 (MET)
jwieck@debis.com (Jan Wieck)
Re: [HACKERS] view?
Jan Wieck wrote:
> 
> Jose' Soares wrote:
> 
> > I modified psql.c to use pg_get_viewdef() function to seek for views and
> > now I can display only tables using \dt
>
>  I suggest not to apply this patch
> 
>  1.  The  function  pg_get_viewdef()  is  definitely  too much
>      overhead. In fact it must parse back  the  complete  view
>      .......

I used pg_get_viewdef() function to properly detect views and tables in
PgAccess.
For the moment, I have released a new version 0.94 of PgAccess based on
this and it works fine.

I am sure that you are right concerning pg_get_viewdef() function, but
please, could you tell me another way of detecting views from "false
views" ? relhasrules field isn't good enough for it and for the moment,
pg_get_viewdef() seems to be a good method. If anyone could tell me
another way of safely detecting views I can change it.

Also, I used pg_get_viewdef() in order to get views's definition for the
"Design" view function so, I will need also such a function in order to
implement this feature.

-- 
Constantin Teodorescu
FLEX Consulting Braila, ROMANIA

Sat, 23 Jan 1999 01:11:07 +0200
Constantin Teodorescu <teo@flex.ro>
Re: [HACKERS] view?


Constantin Teodorescu ha scritto:

> Jan Wieck wrote:
> >
> > Jose' Soares wrote:
> >
> > > I modified psql.c to use pg_get_viewdef() function to seek for views and
> > > now I can display only tables using \dt
> >
> >  I suggest not to apply this patch
> >
> >  1.  The  function  pg_get_viewdef()  is  definitely  too much
> >      overhead. In fact it must parse back  the  complete  view
> >      .......
>
> I used pg_get_viewdef() function to properly detect views and tables in
> PgAccess.
> For the moment, I have released a new version 0.94 of PgAccess based on
> this and it works fine.
>
> I am sure that you are right concerning pg_get_viewdef() function, but
> please, could you tell me another way of detecting views from "false
> views" ? relhasrules field isn't good enough for it and for the moment,
> pg_get_viewdef() seems to be a good method. If anyone could tell me
> another way of safely detecting views I can change it.
>
> Also, I used pg_get_viewdef() in order to get views's definition for the
> "Design" view function so, I will need also such a function in order to
> implement this feature.
>
> --
> Constantin Teodorescu
> FLEX Consulting Braila, ROMANIA

I'm not sure if we may consider  good the pg_views data.
If so you can check for views into it, as..

hygea=> \d pg_views

Table    = pg_views
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| viewname                         | name                             |    32 |
| viewowner                        | name                             |    32 |
| definition                       | text                             |   var |
+----------------------------------+----------------------------------+-------+

hygea=> \dv
Database    = hygea
 +------------------+----------------------------------+----------+
 |  Owner           |             Relation             |   Type   |
 +------------------+----------------------------------+----------+
 | postgres         | wattivita                        | view     |
 | postgres         | wtabelle                         | view     |
 +------------------+----------------------------------+----------+

hygea=> select 'yes' from pg_views where viewname='wattivita';
?column?
--------
yes
(1 row)

-Jose'-


Fri, 12 Feb 1999 14:53:21 +0100
"Jose' Soares" <jose@sferacarta.com>
Re: [HACKERS] view?