view? - Mailing list pgsql-hackers

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>