view?
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'-
Attachments:
psql.patchtext/plain; charset=us-ascii; name=psql.patchDownload
*** ./postgresql-v6.4/src/bin/psql/psql.c.orig Mon Oct 26 02:04:37 1998
--- ./postgresql-v6.4/src/bin/psql/psql.c Fri Jan 22 16:05:17 1999
***************
*** 249,263 ****
fprintf(fout, " \\C [<captn>] -- set html3 caption (currently '%s')\n", pset->opt.caption ? pset->opt.caption : "");
fprintf(fout, " \\connect <dbname|-> <user> -- connect to new database (currently '%s')\n", PQdb(pset->db));
fprintf(fout, " \\copy table {from | to} <fname>\n");
! fprintf(fout, " \\d [<table>] -- list tables and indices, columns in <table>, 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 [<fname>] -- edit the current query buffer or <fname>\n");
fprintf(fout, " \\E [<fname>] -- edit the current query buffer or <fname>, and execute\n");
--- 249,264 ----
fprintf(fout, " \\C [<captn>] -- set html3 caption (currently '%s')\n", pset->opt.caption ? pset->opt.caption : "");
fprintf(fout, " \\connect <dbname|-> <user> -- connect to new database (currently '%s')\n", PQdb(pset->db));
fprintf(fout, " \\copy table {from | to} <fname>\n");
! fprintf(fout, " \\d [<table>] -- list tables, views and indices, columns in <table> 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 [<fname>] -- edit the current query buffer or <fname>\n");
fprintf(fout, " \\E [<fname>] -- edit the current query buffer or <fname>, 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 out the 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't find 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];
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 \dtI 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 \dtI 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'-