Обсуждение: view?

Hi, <p>Currently psql show views like:<tt></tt><p><tt>Database    = hygea</tt><br
/><tt> +------------------+----------------------------------+----------+</tt><br/><tt> |  Owner          
|            Relation             |   Type   |</tt><br
/><tt> +------------------+----------------------------------+----------+</tt><br/><tt> | postgres         |
abbattimenti                    | table    |</tt><br /><tt> | postgres         | wattivita                        |
view?   |</tt><br /><tt> | postgres         | attivita_a                       | table    |</tt><p>because it seeks for
relhasrulesfield and if you have a table (not a table) with a rule it thinks it is a view <br />and displays "view?"
insteadof "table". <p>I modified psql.c to use pg_get_viewdef() function to seek for views and now I can display only
tablesusing \dt <br />or only views using \dv like:hygea=> \dv <p>\dv <br /><tt>Database    = hygea</tt><br
/><tt> +------------------+----------------------------------+----------+</tt><br/><tt> |  Owner          
|            Relation             |   Type   |</tt><br
/><tt> +------------------+----------------------------------+----------+</tt><br/><tt> | postgres         |
wattivita                       | view     |</tt><br /><tt> | postgres         | wtabelle                         |
view    |</tt><br
/><tt> +------------------+----------------------------------+----------+</tt><tt></tt><p><tt>\dt</tt><br
/><tt>Database   = hygea</tt><br /><tt> +------------------+----------------------------------+----------+</tt><br
/><tt> | Owner           |             Relation             |   Type   |</tt><br
/><tt> +------------------+----------------------------------+----------+</tt><br/><tt> | postgres         |
abbattimenti                    | table    |</tt><br /><tt> | postgres         | attivita                         |
table   |</tt><br /><tt> | postgres         | attivita_a                       | table    |</tt><br /><tt> |
postgres        | attivita_b                       | table    |</tt><br /><tt> | postgres         |
brogliacci                      | table    |</tt><br /><tt> | postgres         | capi                             |
table   |</tt><br /><tt> | postgres         | comuni                           | table    |</tt><br
/><tt> +------------------+----------------------------------+----------+</tt><tt></tt><p>Ifthis interests to someone
thereis the attached patch. <p>-Jose'- <br />  <br />  <br />  *** ./postgresql-v6.4/src/bin/psql/psql.c.orig Mon Oct
2602:04:37 1998 --- ./postgresql-v6.4/src/bin/psql/psql.c Fri Jan 22 16:05:17 1999 *************** *** 249,263 ****
fprintf(fout," \\C [] -- set html3 caption (currently '%s')\n", pset->opt.caption ? pset->opt.caption : "");
fprintf(fout," \\connect   -- connect to new database (currently '%s')\n", PQdb(pset->db)); fprintf(fout, " \\copy
table{from | to} \n"); ! fprintf(fout, " \\d [<table><tr><td>] -- list tables and indices, columns in <table><tr><td>,
or* for all\n"); fprintf(fout, " \\da -- list aggregates\n"); fprintf(fout, " \\dd [<object>]- list comment for table,
field,type, function, or operator.\n"); fprintf(fout, " \\df -- list functions\n"); fprintf(fout, " \\di -- list only
indices\n");fprintf(fout, " \\do -- list operators\n"); fprintf(fout, " \\ds -- list only sequences\n"); !
fprintf(fout," \\dS -- list system tables and indexes\n"); fprintf(fout, " \\dt -- list only tables\n"); fprintf(fout,
"\\dT -- list types\n"); fprintf(fout, " \\e [] -- edit the current query buffer or \n"); fprintf(fout, " \\E [] --
editthe current query buffer or , and execute\n"); --- 249,264 ---- fprintf(fout, " \\C [] -- set html3 caption
(currently'%s')\n", pset->opt.caption ? pset->opt.caption : ""); fprintf(fout, " \\connect   -- connect to new
database(currently '%s')\n", PQdb(pset->db)); fprintf(fout, " \\copy table {from | to} \n"); ! fprintf(fout, " \\d
[<table><tr><td>]-- list tables, views and indices, columns in <table><tr><td> or * for all\n"); fprintf(fout, " \\da
--list aggregates\n"); fprintf(fout, " \\dd [<object>]- list comment for table, field, type, function, or
operator.\n");fprintf(fout, " \\df -- list functions\n"); fprintf(fout, " \\di -- list only indices\n"); fprintf(fout,
"\\do -- list operators\n"); fprintf(fout, " \\ds -- list only sequences\n"); ! fprintf(fout, " \\dS -- list system
tables,views and indexes\n"); fprintf(fout, " \\dt -- list only tables\n"); + fprintf(fout, " \\dv -- list only
views\n");fprintf(fout, " \\dT -- list types\n"); fprintf(fout, " \\e [] -- edit the current query buffer or \n");
fprintf(fout," \\E [] -- edit the current query buffer or , and execute\n"); *************** *** 388,394 **** ---
389,398---- int i; char *rk; char *rr; + bool is_view; + int is_table = 2; PGresult *res; + PGresult *rview; int
usePipe= 0; char *pagerenv; FILE *fout; *************** *** 413,418 **** --- 417,427 ---- { case 't': strcat(listbuf,
"WHERE( relkind = 'r') "); + is_table=1; + break; + case 'v': + strcat(listbuf, "WHERE ( relkind = 'r') "); +
is_table=0;break; case 'i': strcat(listbuf, "WHERE ( relkind = 'i') "); *************** *** 423,428 **** --- 432,438
----case 'b': default: strcat(listbuf, "WHERE ( relkind = 'r' OR relkind = 'i' OR relkind = 'S') "); + is_table=2;
break;} if (!system_tables) *************** *** 485,501 **** /* next, print out the instances */ for (i = 0; i <
PQntuples(res);i++) { ! fprintf(fout, " | %-16.16s", PQgetvalue(res, i, 0)); ! fprintf(fout, " | %-32.32s | ",
PQgetvalue(res,i, 1)); ! rk = PQgetvalue(res, i, 2); ! rr = PQgetvalue(res, i, 3); ! if (strcmp(rk, "r") == 0) !
fprintf(fout,"%-8.8s |", (rr[0] == 't') ? "view?" : "table"); ! else if (strcmp(rk, "i") == 0) fprintf(fout, "%-8.8s
|","index"); ! else fprintf(fout, "%-8.8s |", "sequence"); ! fprintf(fout, "\n"); } fprintf(fout, "
+------------------+----------------------------------+----------+\n");PQclear(res); --- 495,521 ---- /* next, print
outthe instances */ for (i = 0; i < PQntuples(res); i++) { ! listbuf[0] = '\0'; ! sprintf(listbuf,"SELECT
substr(pg_get_viewdef('%s'),1,1)",PQgetvalue(res,i,1));! rview = PSQLexec(pset, listbuf); ! if(strcmp(PQgetvalue(rview,
0,0),"N")) ! is_view=TRUE; ! else ! is_view=FALSE; ! if((is_view && !is_table)||(!is_view && is_table)
||is_table==2) ! { ! fprintf(fout, " | %-16.16s", PQgetvalue(res, i, 0)); ! fprintf(fout, " | %-32.32s | ",
PQgetvalue(res,i, 1)); ! rk = PQgetvalue(res, i, 2); ! rr = PQgetvalue(res, i, 3); ! if (strcmp(rk, "r") == 0) !
fprintf(fout,"%-8.8s |", is_view ? "view" : "table"); ! else if (strcmp(rk, "i") == 0) fprintf(fout, "%-8.8s |",
"index");! else fprintf(fout, "%-8.8s |", "sequence"); ! fprintf(fout, "\n"); ! } } fprintf(fout, "
+------------------+----------------------------------+----------+\n");PQclear(res); *************** *** 516,521 ****
---536,544 ---- case 't': fprintf(stderr, "Couldn't find any tables!\n"); break; + case 'v': + fprintf(stderr,
"Couldn'tfind any views!\n"); + break; case 'i': fprintf(stderr, "Couldn't find any indices!\n"); break;
****************** 524,530 **** break; case 'b': default: ! fprintf(stderr, "Couldn't find any tables, sequences or
indices!\n");break; } return -1; --- 547,553 ---- break; case 'b': default: ! fprintf(stderr, "Couldn't find any
tables,views, sequences or indices!\n"); break; } return -1; *************** *** 2050,2055 **** --- 2073,2081 ---- else
if(strncmp(cmd, "dt", 2) == 0) /* only tables */ tableList(pset, false, 't', false); + else if (strncmp(cmd, "dv", 2)
==0) + /* only views */ + tableList(pset, false, 'v', false); else if (strncmp(cmd, "dT", 2) == 0) { char
descbuf[4096];</object></td></tr></table></td></tr></table></object></td></tr></table></td></tr></table> 

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) #

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

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'-