Thread: Have \d show child tables that inherit from the specified parent
Hello,
Last week, i took some time to check if i was still able to write some
basic C code. So i looked into the TODO list and picked some trivial items.
This one is very basic, it just shows the child tables of a specific
table when you type \d <tablename> in psql :
# create table mother(id SERIAL);
# create table daughter() inherits(mother);
# create table daughter2() inherits(mother);
# create schema plop;
# create table plop.daughter3() inherits(mother);
# \d mother
Table "public.mother"
Column | Type | Modifiers
--------+---------+-----------------------------------------------------
id | integer | not null default nextval('mother_id_seq'::regclass)
Child tables: daughter,
daughter2,
plop.daughter3
I understand that it may not be the best moment to submit such a trivial
patch, as most of you are working hard on more important topics. If you
think i should submit it later, just let me know.
BTW, this is the first patch i submit so any comment is more than welcome.
Regards,
--
damien clochard
dalibo.com | dalibo.org
? logfile
? psql
Index: describe.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.199
diff -c -r1.199 describe.c
*** describe.c 11 Feb 2009 19:12:04 -0000 1.199
--- describe.c 25 Feb 2009 22:53:50 -0000
***************
*** 1668,1673 ****
--- 1668,1699 ----
}
PQclear(result);
+ /* print child tables */
+ printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits
iWHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY i.inhseqno;", oid);
+
+ result = PSQLexec(buf.data, false);
+ if (!result)
+ goto error_return;
+ else
+ tuples = PQntuples(result);
+
+ for (i = 0; i < tuples; i++)
+ {
+ const char *s = _("Child tables");
+
+ if (i == 0)
+ printfPQExpBuffer(&buf, "%s: %s", s, PQgetvalue(result, i, 0));
+ else
+ printfPQExpBuffer(&buf, "%*s %s", (int) strlen(s), "", PQgetvalue(result, i, 0));
+ if (i < tuples - 1)
+ appendPQExpBuffer(&buf, ",");
+
+ printTableAddFooter(&cont, buf.data);
+ }
+ PQclear(result);
+
+
+
if (verbose)
{
const char *s = _("Has OIDs");
On Thu, Feb 26, 2009 at 12:25:08AM +0100, damien clochard wrote:
> Hello,
>
> Last week, i took some time to check if i was still able to write
> some basic C code. So i looked into the TODO list and picked some
> trivial items.
>
> This one is very basic, it just shows the child tables of a specific
> table when you type \d <tablename> in psql :
>
>
> # create table mother(id SERIAL);
> # create table daughter() inherits(mother);
> # create table daughter2() inherits(mother);
> # create schema plop;
> # create table plop.daughter3() inherits(mother);
> # \d mother
> Table "public.mother"
> Column | Type | Modifiers
> --------+---------+-----------------------------------------------------
> id | integer | not null default nextval('mother_id_seq'::regclass)
> Child tables: daughter,
> daughter2,
> plop.daughter3
>
>
> I understand that it may not be the best moment to submit such a
> trivial patch, as most of you are working hard on more important
> topics. If you think i should submit it later, just let me know.
>
> BTW, this is the first patch i submit so any comment is more than
> welcome.
Should the patch (and the feature) use WITH RECURSIVE in order to get
the entire tree?
Cheers,
David (yes, I know it's predictable from me, but WITH RECURSIVE really
is a neat new toy ;)
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> Should the patch (and the feature) use WITH RECURSIVE in order to get
> the entire tree?
See the note at the top of that file that all queries are expected to
work with server versions back to 7.4.
regards, tom lane
On Wed, Feb 25, 2009 at 07:30:30PM -0500, Tom Lane wrote: > David Fetter <david@fetter.org> writes: > > Should the patch (and the feature) use WITH RECURSIVE in order to > > get the entire tree? > > See the note at the top of that file that all queries are expected > to work with server versions back to 7.4. Would it be OK to have a different code path for pre-8.4 databases? Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> On Wed, Feb 25, 2009 at 07:30:30PM -0500, Tom Lane wrote:
>> See the note at the top of that file that all queries are expected
>> to work with server versions back to 7.4.
> Would it be OK to have a different code path for pre-8.4 databases?
I don't really see the point here. describe.c exists to get the job
done, not to show off fancy features.
BTW, has anyone thought about what's gonna happen if the table has got
thousands of inheritance children?
regards, tom lane
Re: Have \d show child tables that inherit from the specified parent
From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > This one is very basic, it just shows the child tables of a specific > table when you type \d <tablename> in psql : I'm not so jazzed about this, as I work on systems that have literally hundreds of child tables. When I do a \d on the master table, that's not what I want to see. Frankly, a good naming system goes a long way towards that, as children usually line up quick neatly in \d output. Can we make this only showup in \d+ perhaps? Or not do this at all? Or for \d, make it say "Use \d+ to view all children"? - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200902261222 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkmm0AEACgkQvJuQZxSWSsjKSgCgrWjNIewEWmhZbIn1qO8fUQNf U4sAn2+AYLnCtq02e8s9IVA55EmgRlk3 =Y9Pi -----END PGP SIGNATURE-----
On Thu, Feb 26, 2009 at 12:23 PM, Greg Sabino Mullane <greg@turnstep.com> wrote: >> This one is very basic, it just shows the child tables of a specific >> table when you type \d <tablename> in psql : > > I'm not so jazzed about this, as I work on systems that have literally > hundreds of child tables. When I do a \d on the master table, that's > not what I want to see. Frankly, a good naming system goes a long > way towards that, as children usually line up quick neatly in \d > output. Can we make this only showup in \d+ perhaps? Or not do > this at all? Or for \d, make it say "Use \d+ to view all children"? Yeah, there is already an awful lot of output from regular old \d Typically, I get indexes, check constraints, foreign key constraints, and triggers. It's not real easy to read. It would be nice if there were a way to suppress this output or only show the part you're interested in at the moment, but it's hard to think of what syntax might be reasonable. ...Robert
Greg Sabino Mullane wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>
>> This one is very basic, it just shows the child tables of a specific
>> table when you type \d <tablename> in psql :
>
> I'm not so jazzed about this, as I work on systems that have literally
> hundreds of child tables.
hi everyone.
Thanks for your comments.
I must admit that i didn't asked myself much questions when i
implemented that feature. As i said i picked that item almost randomly
in the todo list. I choosed to show child table on \d instead \d+ in
order to be consistant with the fact that \d shows the mother of the
specified table.
That being said i recognize that people who have dozens of child tables
might get annoyed by this patch.
Here's 3 ideas that could make things better :
1- When using \d , only show the numbers of child tables
2- Only display the list of child table when \d+ is used. (mode ???)
3- Display the child table names in line, instead of one line per table.
Here's what it would look like :
# \d mother Table "public.mother" Column | Type | Modifiers
--------+---------+----------------------------------------------------- id | integer | not null default
nextval('mother_id_seq'::regclass)
This table has 5 child tables. Use \d+ to display them.
#\d+ mother Table "public.mother" Column | Type | Modifiers
--------+---------+----------------------------------------------------- id | integer | not null default
nextval('mother_id_seq'::regclass)
Child tables : daughter1, daughter2, daughter3, daughter4, daughter5
Does it look better ?
--
damien clochard
dalibo.org | dalibo.com
Re: Have \d show child tables that inherit from the specified parent
From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > That being said i recognize that people who have dozens of child tables > might get annoyed by this patch. Dozens is on the low end: there are often a dozen dozen or more. > Here's 3 ideas that could make things better : > > 1- When using \d , only show the numbers of child tables > > 2- Only display the list of child table when \d+ is used. (mode ???) I like these two ideas > 3- Display the child table names in line, instead of one line per table. No, this would be wrong. Please keep them one per line, and sort them by name. - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200903230841 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAknHg2YACgkQvJuQZxSWSsj3fQCfY15MYLjGCELNAXhQWR10grJf +c4AoK/3sxtCNe4N+exmYx22nM4Z9RvD =clRO -----END PGP SIGNATURE-----
Re: Have \d show child tables that inherit from the specified parent
From
"damien@dalibo.info"
Date:
Hi,
Here's a second version. Main changes are :
* Child tables are sorted by name
* \d only shows the number of child tables
* \d+ shows the full list
Which gives :
postgres=# \d mother
Table "public.mother"
Column | Type | Modifiers
--------+---------+-----------------------------------------------------
id | integer | not null default nextval('mother_id_seq'::regclass)
This table has 4 child table(s). Use \d+ to display them.
postgres=# \d+ mother
Table "public.mother"
Column | Type | Modifiers
| Storage | Description
--------+---------+-----------------------------------------------------+---------+-------------
id | integer | not null default nextval('mother_id_seq'::regclass)
| plain |
Child tables: daughter,
daughter2,
daughter3,
plop.daughter4
Has OIDs: no
For people having hundreds of child tables, the use of \d+ will still be
uncomfortable. But i must admit that i can't see any better way to
implement this feature, except by adding a new syntax ( \dh to show
specific information about inheritance ? )
Anyway let me know if you think it is not worth going forward on that
feature :-)
--
damien clochard
http://dalibo.org | http://dalibo.com
? logfile
Index: describe.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.212
diff -c -r1.212 describe.c
*** describe.c 5 May 2009 02:29:06 -0000 1.212
--- describe.c 9 May 2009 23:57:36 -0000
***************
*** 1812,1817 ****
--- 1812,1856 ----
}
PQclear(result);
+ /* print child tables */
+ printfPQExpBuffer(&buf, "SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits
iWHERE c.oid=i.inhrelid AND i.inhparent = '%s' ORDER BY c.oid::pg_catalog.regclass;", oid);
+
+ result = PSQLexec(buf.data, false);
+ if (!result)
+ goto error_return;
+ else
+ tuples = PQntuples(result);
+
+ if (!verbose)
+ {
+ /* print the number of child tables, if any */
+ if (tuples > 0)
+ {
+ printfPQExpBuffer(&buf, _("This table has %i child table(s). Use \\d+ to display them."),tuples);
+ printTableAddFooter(&cont, buf.data);
+ }
+ }
+ else
+ {
+ /* display the list of child tables*/
+ for (i = 0; i < tuples; i++)
+ {
+ const char *ct = _("Child tables");
+
+ if (i == 0)
+ printfPQExpBuffer(&buf, "%s: %s", ct, PQgetvalue(result, i, 0));
+ else
+ printfPQExpBuffer(&buf, "%*s %s", (int) strlen(ct), "", PQgetvalue(result, i,
0));
+ if (i < tuples - 1)
+ appendPQExpBuffer(&buf, ",");
+
+ printTableAddFooter(&cont, buf.data);
+ }
+ }
+ PQclear(result);
+
+
+
if (verbose)
{
const char *s = _("Has OIDs");
Re: Have \d show child tables that inherit from the specified parent
From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > * Child tables are sorted by name > * \d only shows the number of child tables > * \d+ shows the full list > This table has 4 child table(s). Use \d+ to display them. Would be nice if you could get rid of that ugly '(s)' part. > Child tables: daughter, > daughter2, > daughter3, > plop.daughter4 > Has OIDs: no Since the child list can be rather long, it's probably better to put everything else first, e.g. the "Has OIDs" line. > For people having hundreds of child tables, the use of \d+ will still be > uncomfortable. But i must admit that i can't see any better way to > implement this feature, except by adding a new syntax ( \dh to show > specific information about inheritance ? ) I think the \d+ is a good compromise. - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200905102120 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAkoHfZ8ACgkQvJuQZxSWSsjIiACfWHH+YbUPMxanDdUTprbtHz+T FzwAn1GVzEArQLAPSJIAKWnMXGHvpdG1 =+oo1 -----END PGP SIGNATURE-----
Re: Have \d show child tables that inherit from the specified parent
From
Euler Taveira de Oliveira
Date:
Greg Sabino Mullane escreveu: > >> * Child tables are sorted by name >> * \d only shows the number of child tables >> * \d+ shows the full list > >> This table has 4 child table(s). Use \d+ to display them. > > Would be nice if you could get rid of that ugly '(s)' part. > What about "Child Tables: 4"? It is short and simple. >> Child tables: daughter, >> daughter2, >> daughter3, >> plop.daughter4 >> Has OIDs: no > > Since the child list can be rather long, it's probably better to put > everything else first, e.g. the "Has OIDs" line. > +1. Don't forget "Options". -- Euler Taveira de Oliveira http://www.timbira.com/
On Sunday 10 May 2009 03:05:48 damien@dalibo.info wrote: > Here's a second version. Main changes are : > > * Child tables are sorted by name > * \d only shows the number of child tables > * \d+ shows the full list Committed.
Peter Eisentraut <peter_e@gmx.net> writes:
> On Sunday 10 May 2009 03:05:48 damien@dalibo.info wrote:
>> Here's a second version. Main changes are :
>>
>> * Child tables are sorted by name
>> * \d only shows the number of child tables
>> * \d+ shows the full list
> Committed.
I looked at this patch. I'm a bit disturbed by theORDER BY c.oid::pg_catalog.regclass
business. I doubt that does what the author thinks, and I'm not sure
what we really want anyway. If we want the child table names sorted
alphabetically then the regclass value needs to be further cast to text,
ieORDER BY c.oid::pg_catalog.regclass::pg_catalog.text
As-is, it's sorting by the OID values, which is almost like sorting the
children in creation order, which might possibly be thought preferable
... except that once the OID counter wraps around we won't be able to
guarantee that anymore.
Comments?
regards, tom lane
On Tuesday 07 July 2009 19:35:54 Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > On Sunday 10 May 2009 03:05:48 damien@dalibo.info wrote: > >> Here's a second version. Main changes are : > >> > >> * Child tables are sorted by name > >> * \d only shows the number of child tables > >> * \d+ shows the full list > > > > Committed. > > I looked at this patch. I'm a bit disturbed by the > ORDER BY c.oid::pg_catalog.regclass > business. I doubt that does what the author thinks, and I'm not sure > what we really want anyway. If we want the child table names sorted > alphabetically then the regclass value needs to be further cast to text, > ie > ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text Fixed; it was clearly meant to be by name. Creation order might be useful, but we don't really support that anywhere else.
Peter Eisentraut <peter_e@gmx.net> writes:
> On Tuesday 07 July 2009 19:35:54 Tom Lane wrote:
>> I looked at this patch. I'm a bit disturbed by the
>> ORDER BY c.oid::pg_catalog.regclass
>> business. I doubt that does what the author thinks, and I'm not sure
>> what we really want anyway. If we want the child table names sorted
>> alphabetically then the regclass value needs to be further cast to text,
>> ie
>> ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text
> Fixed; it was clearly meant to be by name.
Testing shows that we still have a problem: the query now fails outright
with pre-8.3 servers:
regression=# \d+ a
ERROR: cannot cast type regclass to text
Perhaps it would be sufficient to ORDER BY c.relname.
regards, tom lane