Thread: psql questions: SQL, progname, copyright dates

psql questions: SQL, progname, copyright dates

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I was looking through the code of psql and had a few questions:
1. Do we want to use quote_ident on object names? Ideally, column
names with a space in them, for example, should be surrounded by
double quotes.
2. There is a disconnect between what \? says: \d [NAME]      describe table, index, sequence, or view \d{t|i|s|v|S}
[PATTERN](add "+" for more detail)                list tables/indexes/sequences/views/system tables
 
and the actual default case: WHERE c.relkind IN ('r','v','S','')
Should we include indexes by default? If not, the help file should
be clearer about what happens when no arg is given to \d.
3. I'd like to rearrange the ORDER BY on some objects to show
user-created objects before system ones. Currently, if I create
a new function and do a \df, I have to wade through all the
system functions in the pg_catalog schema before seeing mine.
This seems to be solely because "public" comes after "pg_catalog"
alphabetically.
4. Some of the ORDER BY clauses are incomplete, and not all are
consistent with each other. Most seem to be ordering by schema name,
then object name. If this the direction we want all of them to
go? (e.g. \df)
5. psql from cvs was coredumping on me earlier today when psql_error
was called. I tracked it down to the invoking of the pset.progname
variable in fprintf, but the problem fixed itself on the next make,
so I don't have more detail than that. I'd appreciate if someone could
check out the 'progname' var and see if I overlooked something.
6. Can someone run that magic year-changing update script mentioned
earlier? The dates should be 2004, not 2003.
Thanks,
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200408271906
-----BEGIN PGP SIGNATURE-----
iD8DBQFBL8FfvJuQZxSWSsgRAifeAKC8cTVXJLRe5oj87b18E2VFAY6B2ACg/L3s
lYrIEDfDZWANnafPiFx/bBc=
=8lO2
-----END PGP SIGNATURE-----




Re: psql questions: SQL, progname, copyright dates

From
Jon Jensen
Date:
On Fri, 27 Aug 2004, Greg Sabino Mullane wrote:

> 3. I'd like to rearrange the ORDER BY on some objects to show
> user-created objects before system ones. Currently, if I create
> a new function and do a \df, I have to wade through all the
> system functions in the pg_catalog schema before seeing mine.
> This seems to be solely because "public" comes after "pg_catalog"
> alphabetically.

I would appreciate that greatly. I've often found it difficult to find 
functions because of the current sorting.

Jon


Re: psql questions: SQL, progname, copyright dates

From
Bruce Momjian
Date:
Greg Sabino Mullane wrote:
[ There is text before PGP section. ]
> 
[ PGP not available, raw data follows ]
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>  
>  
> I was looking through the code of psql and had a few questions:
>  
> 1. Do we want to use quote_ident on object names? Ideally, column
> names with a space in them, for example, should be surrounded by
> double quotes.

Example of it failing?  You mean like \d tablename?

>  
> 2. There is a disconnect between what \? says:
>  
>   \d [NAME]      describe table, index, sequence, or view
>   \d{t|i|s|v|S} [PATTERN] (add "+" for more detail)
>                  list tables/indexes/sequences/views/system tables
>  
> and the actual default case:
>  
>   WHERE c.relkind IN ('r','v','S','')
>  
> Should we include indexes by default? If not, the help file should
> be clearer about what happens when no arg is given to \d.

The issue is that while \d doesn't list indexes, \d indexname works. 
Any idea how to clarify that?

> 3. I'd like to rearrange the ORDER BY on some objects to show
> user-created objects before system ones. Currently, if I create
> a new function and do a \df, I have to wade through all the
> system functions in the pg_catalog schema before seeing mine.
> This seems to be solely because "public" comes after "pg_catalog"
> alphabetically.

Well, at least they are together at the bottom.

> 4. Some of the ORDER BY clauses are incomplete, and not all are
> consistent with each other. Most seem to be ordering by schema name,
> then object name. If this the direction we want all of them to
> go? (e.g. \df)

I would think so, yea.

> 5. psql from cvs was coredumping on me earlier today when psql_error
> was called. I tracked it down to the invoking of the pset.progname
> variable in fprintf, but the problem fixed itself on the next make,
> so I don't have more detail than that. I'd appreciate if someone could
> check out the 'progname' var and see if I overlooked something.

Not sure.

> 6. Can someone run that magic year-changing update script mentioned
> earlier? The dates should be 2004, not 2003.

Running now. Thanks.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: psql questions: SQL, progname, copyright dates

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message
>> 1. Do we want to use quote_ident on object names? Ideally, column
>> names with a space in them, for example, should be surrounded by
>> double quotes.
> Example of it failing?  You mean like \d tablename?
Yes: CREATE TABLE "Upper Division" ( abc              int, " spaced out  "  text, " real ""Name""" text, "MixedCase"
 text
 
);
psql=> \d U*
Did not find any relation named "U*".
(hmm...)
psql=> \d "Upper Division"
Table "public.Upper Division"   Column     |  Type   | Modifiers
---------------+---------+-----------abc           | integer | spaced out   | text    | real "Name"  | text
|MixedCase    | text    |
 
This should be:
Table "public.Upper Division"     Column      |  Type   | Modifiers
------------------+---------+-----------abc              | integer |" spaced out  "  | text    |" real ""Name""" | text
  |"MixedCase"      | text    |
 
>> \d [NAME]      describe table, index, sequence, or view
> The issue is that while \d doesn't list indexes, \d indexname works.
> Any idea how to clarify that?
By showing indexes when you do a \d? By clearly stating that \d without
args only shows 3 of the 4 items? I'm not sure.
>> a new function and do a \df, I have to wade through all the
>> system functions in the pg_catalog schema before seeing mine.
> Well, at least they are together at the bottom.
Over 1,500 rows later! :) With a pager, that's a real pain.
--
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200408290833
-----BEGIN PGP SIGNATURE-----
iD8DBQFBMc3HvJuQZxSWSsgRAiL0AKD7E/nT9f1vCuhuh1pZiyvjgUAJEQCgkxDg
jq/ABuHdXb2uDaspHdPJ7ls=
=MCkP
-----END PGP SIGNATURE-----




Re: psql questions: SQL, progname, copyright dates

From
Tom Lane
Date:
"Greg Sabino Mullane" <greg@turnstep.com> writes:
> This should be:
> Table "public.Upper Division"
>       Column      |  Type   | Modifiers
> ------------------+---------+-----------
>  abc              | integer |
>  " spaced out  "  | text    |
>  " real ""Name""" | text    |
>  "MixedCase"      | text    |

I find this quite ugly, and don't think it's a good change.  Anyone
who's fool enough to use trailing spaces in column names deserves the
pain it will cost them --- and there is no other case in which the
unquoted display is ambiguous.

If we are going to go over to a policy of displaying names the same way
you'd have to type them, what of the table name at the top of the
output?  (I actually would vote for some change there, because I think
the way we are quoting the whole thing is actively misleading...)
For that matter, wouldn't consistency require that column headings
in SELECT results be quoted similarly?

On the ordering issues, I'd go for straight alphabetical schema/name
sort in all cases; I thought that's where we were already, but if you
see some missing cases let's fix it.  I'm not enamored of discriminating
against system objects in the sort order.
        regards, tom lane


Re: psql questions: SQL, progname, copyright dates

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> I find this quite ugly, and don't think it's a good change.  Anyone
> who's fool enough to use trailing spaces in column names deserves the
> pain it will cost them --- and there is no other case in which the
> unquoted display is ambiguous.
I agree, just wanted to see how pedantic we were going to be. As long
as nobody is using the output of psql for input into a script, we
should be fine.
> If we are going to go over to a policy of displaying names the same way
> you'd have to type them, what of the table name at the top of the
> output?  (I actually would vote for some change there, because I think
> the way we are quoting the whole thing is actively misleading...)
Agreed: I'll remove that odd quoting in my next patch if nobody else obejcts.
> For that matter, wouldn't consistency require that column headings
> in SELECT results be quoted similarly?
Yes, that would be consistently ugly. :)
> On the ordering issues, I'd go for straight alphabetical schema/name
> sort in all cases; I thought that's where we were already, but if you
> see some missing cases let's fix it.  I'm not enamored of discriminating
> against system objects in the sort order.
I don't agree with this. We already discriminate against system objects by
not showing system tables, for example, when doing a \dt. There is no reason
I should have to scroll through 1500+ system functions using \df because I
forgot the name of one of the 12 functions I've created, or simply because
I want to view all 12 quickly.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200408291827
-----BEGIN PGP SIGNATURE-----
iD8DBQFBMlj2vJuQZxSWSsgRAtOIAJ4mpzC/pSzDcVSjRSecwoGVYUOScgCg150m
m4qpZuc7wT0998bNyPQWU6E=
=yXLQ
-----END PGP SIGNATURE-----




Re: psql questions: SQL, progname, copyright dates

From
Robert Treat
Date:
On Sunday 29 August 2004 18:29, Greg Sabino Mullane wrote:
>
> > On the ordering issues, I'd go for straight alphabetical schema/name
> > sort in all cases; I thought that's where we were already, but if you
> > see some missing cases let's fix it.  I'm not enamored of discriminating
> > against system objects in the sort order.
>
> I don't agree with this. We already discriminate against system objects by
> not showing system tables, for example, when doing a \dt. There is no
> reason I should have to scroll through 1500+ system functions using \df
> because I forgot the name of one of the 12 functions I've created, or
> simply because I want to view all 12 quickly.

On the one hand I would have to agree with Greg, and I would point out that by 
default in phppgadmin we don't display system functions since it is just too 
overbearing. 

OTOH I know that I sometimes use /df to look up built in functions that I 
don't exactly remember, so keeping a way to access the system functions is 
handy. 

Given a third hand I'd look for some way to do both... perhaps \df can show 
all functions and \df- can show only user created functions?

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


Re: psql questions: SQL, progname, copyright dates

From
Greg Stark
Date:
Robert Treat <xzilla@users.sourceforge.net> writes:

> OTOH I know that I sometimes use /df to look up built in functions that I 
> don't exactly remember, so keeping a way to access the system functions is 
> handy. 
> 
> Given a third hand I'd look for some way to do both... perhaps \df can show 
> all functions and \df- can show only user created functions?

Well there's always \dtS and \dvS I don't see why typing \dfS is any harder.

It would be nice for this to be more visible in the documentation and the \?
output though. I've only just found it after months of pulling hair out
looking for something just like it.

-- 
greg



Re: psql questions: SQL, progname, copyright dates

From
Robert Treat
Date:
On Mon, 2004-08-30 at 03:28, Greg Stark wrote:
> Robert Treat <xzilla@users.sourceforge.net> writes:
> 
> > OTOH I know that I sometimes use /df to look up built in functions that I 
> > don't exactly remember, so keeping a way to access the system functions is 
> > handy. 
> > 
> > Given a third hand I'd look for some way to do both... perhaps \df can show 
> > all functions and \df- can show only user created functions?
> 
> Well there's always \dtS and \dvS I don't see why typing \dfS is any harder.
> 
> It would be nice for this to be more visible in the documentation and the \?
> output though. I've only just found it after months of pulling hair out
> looking for something just like it.
> 

Wow you're not kidding. I've been using postgresql for I don't know how
many years and I don't think I'd ever noticed that before.  ISTM that we
can make \df return only user function and \dfS return the system
functions, and this would be consitant with how we handle other options.
Just make sure to reword \? out put to make it clear that adding S will
show system objects. 

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL



Re: psql questions: SQL, progname, copyright dates

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Greg Stark wrote:
> Well there's always \dtS and \dvS I don't see why typing \dfS is any harder.
>
> It would be nice for this to be more visible in the documentation and the \?
> output though. I've only just found it after months of pulling hair out
> looking for something just like it
Robert Treat replied:
> Wow you're not kidding. I've been using postgresql for I don't know how
> many years and I don't think I'd ever noticed that before.  ISTM that we
> can make \df return only user function and \dfS return the system
> functions, and this would be consitant with how we handle other options.
> Just make sure to reword \? out put to make it clear that adding S will
> show system objects.
So it seems there are two possible solutions to the problem of segregating
user and system objects: change the order by or change the backslash operators.
I like the latter way, as it seems consistent with what we already are doing
(e.g. \dt \di) How about if we change the rest of the \d operators that support
custom objects to support the "S" option? I would affect the following:
\da \dc \dd \df \do \dT
I would rewrite the \? docs to make this more clear as well.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200409051221
-----BEGIN PGP SIGNATURE-----
iD8DBQFBOz0TvJuQZxSWSsgRAmdhAKCke2FfyB58/2dGoQF3Z+qmfNq3swCglmk9
aHa4jh1T2fzfCQ7XRQIlhLM=
=r5Qb
-----END PGP SIGNATURE-----




Re: psql questions: SQL, progname, copyright dates

From
Bruce Momjian
Date:
Greg Sabino Mullane wrote:
[ There is text before PGP section. ]
> 
[ PGP not available, raw data follows ]
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>  
>  
> Greg Stark wrote:
> > Well there's always \dtS and \dvS I don't see why typing \dfS is any harder.
> >
> > It would be nice for this to be more visible in the documentation and the \?
> > output though. I've only just found it after months of pulling hair out
> > looking for something just like it
>  
> Robert Treat replied:
> > Wow you're not kidding. I've been using postgresql for I don't know how
> > many years and I don't think I'd ever noticed that before.  ISTM that we
> > can make \df return only user function and \dfS return the system
> > functions, and this would be consitant with how we handle other options.
> > Just make sure to reword \? out put to make it clear that adding S will
> > show system objects.
>  
> So it seems there are two possible solutions to the problem of segregating
> user and system objects: change the order by or change the backslash operators.
> I like the latter way, as it seems consistent with what we already are doing
> (e.g. \dt \di) How about if we change the rest of the \d operators that support
> custom objects to support the "S" option? I would affect the following:
>  
> \da \dc \dd \df \do \dT
>  
> I would rewrite the \? docs to make this more clear as well.

Agreed it would be nice to more clearly distingush user functions from
system ones, but how?  I can't see how 'S' is going to help us because
\dS already shows system tables.  Would it be \dfS?  What is the logic
to that?  Having 'S' be a flag and a command is too confusing. 

And what about \dn.  Seems showing system schemas vs ordinary schemas
would make sense too.  I wonder if just telling to people focus on the
schema name is the best bet.  

Another idea is to add a flag to skip system stuff like '-', so \df-
doesn't show system stuff.  Same for the others.  That does make sense
to me.  I know it isn't logical for \d but \d is for storage, while the
others are different in that system tables aren't normally accessed by
users, while system functions/schemas are.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: psql questions: SQL, progname, copyright dates

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> Agreed it would be nice to more clearly distingush user functions from
> system ones, but how?  I can't see how 'S' is going to help us because
> \dS already shows system tables.  Would it be \dfS?  What is the logic
> to that?  Having 'S' be a flag and a command is too confusing.
Um...it is already there, in the form of \dtS and \diS. This would merely
elevate some of the lesser-used system objects (e.g. operators, domains)
to the same status as the more common ones (e.g. tables, sequences).
> And what about \dn.  Seems showing system schemas vs ordinary schemas
> would make sense too.  I wonder if just telling to people focus on the
> schema name is the best bet.
Yes, they fall in to my "no differentiation" pile and would not get a
'S' modifier. Simliarly with casts, tablespaces, users, etc.
> Another idea is to add a flag to skip system stuff like '-', so \df-
> doesn't show system stuff.  Same for the others.  That does make sense
> to me.  I know it isn't logical for \d but \d is for storage, while the
> others are different in that system tables aren't normally accessed by
> users, while system functions/schemas are.
The minus flag seems a little ugly to me, when we already have an
established way of differentiating between the two. Sure, people *access*
system functions, but having to look at them through \df seems to be
a fairly rare event, I would think. Far more likely they would want to
view their own functions.
To clarify the 'S' a bit more, here is the output from \? in my new patch:
Informational (S = show system objects) \l                list all databases (add "+" for more detail) \d[S]
list tables, views, and sequences \d[S] NAME        describe table, view, sequence, or index \dt[S] [PATTERN]  list
tables(add "+" for more detail) \dv[S] [PATTERN]  list views (add "+" for more detail) \ds[S] [PATTERN]  list sequences
(add"+" for more detail) \di[S] [PATTERN]  list indexes (add "+" for more detail) \df[S] [PATTERN]  list functions (add
"+"for more detail) \dD[S] [PATTERN]  list domains \do[S] [NAME]     list operators \da[S] [PATTERN]  list aggregate
functions\dT[S] [PATTERN]  list data types (add "+" for more detail) \dc[S] [PATTERN]  list conversions \db [PATTERN]
 list tablespaces (add "+" for more detail) \dC               list casts \dd [PATTERN]     list objects with comments
\dg[PATTERN]     list groups \dl               list large objects, same as \lo_list \dn [PATTERN]     list schemas (add
"+"for more detail) \dp [PATTERN]     list table, view and sequence access privileges \du [PATTERN]     list users
 
Note that the confusing "t|i|v|s|S" thing has been broken up, and
the "S" objects are grouped together.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200409090737
-----BEGIN PGP SIGNATURE-----
iD8DBQFBQECGvJuQZxSWSsgRAgG5AJ9EYY1+s42wBsrF+Q2g1Jpu2F+M2wCgvkB0
/prkBWW1Z7Mqc2KRtDyf0vo=
=8muz
-----END PGP SIGNATURE-----




Re: psql questions: SQL, progname, copyright dates

From
Bruce Momjian
Date:
I talked to Greg via chat and it looks like '&' is the best choice for
adding system object display:
\d& shows system stuff\df& shows system functionsetc.

Greg is going to work on a patch for 8.1.

---------------------------------------------------------------------------

Bruce Momjian wrote:
> Greg Sabino Mullane wrote:
> [ There is text before PGP section. ]
> > 
> [ PGP not available, raw data follows ]
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >  
> >  
> > Greg Stark wrote:
> > > Well there's always \dtS and \dvS I don't see why typing \dfS is any harder.
> > >
> > > It would be nice for this to be more visible in the documentation and the \?
> > > output though. I've only just found it after months of pulling hair out
> > > looking for something just like it
> >  
> > Robert Treat replied:
> > > Wow you're not kidding. I've been using postgresql for I don't know how
> > > many years and I don't think I'd ever noticed that before.  ISTM that we
> > > can make \df return only user function and \dfS return the system
> > > functions, and this would be consitant with how we handle other options.
> > > Just make sure to reword \? out put to make it clear that adding S will
> > > show system objects.
> >  
> > So it seems there are two possible solutions to the problem of segregating
> > user and system objects: change the order by or change the backslash operators.
> > I like the latter way, as it seems consistent with what we already are doing
> > (e.g. \dt \di) How about if we change the rest of the \d operators that support
> > custom objects to support the "S" option? I would affect the following:
> >  
> > \da \dc \dd \df \do \dT
> >  
> > I would rewrite the \? docs to make this more clear as well.
> 
> Agreed it would be nice to more clearly distingush user functions from
> system ones, but how?  I can't see how 'S' is going to help us because
> \dS already shows system tables.  Would it be \dfS?  What is the logic
> to that?  Having 'S' be a flag and a command is too confusing. 
> 
> And what about \dn.  Seems showing system schemas vs ordinary schemas
> would make sense too.  I wonder if just telling to people focus on the
> schema name is the best bet.  
> 
> Another idea is to add a flag to skip system stuff like '-', so \df-
> doesn't show system stuff.  Same for the others.  That does make sense
> to me.  I know it isn't logical for \d but \d is for storage, while the
> others are different in that system tables aren't normally accessed by
> users, while system functions/schemas are.
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: psql questions: SQL, progname, copyright dates

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I talked to Greg via chat and it looks like '&' is the best choice for
> adding system object display:

>     \d& shows system stuff

Yech, that's awful.  It looks ugly and it commandeers a punctuation
symbol that we might wish to use for something else someday.

I thought the "S" suggestion was much better than this.

Personally I am not unhappy with the existing behavior, because (unlike
Greg I guess) I use \df and \do to look at system definitions all the
time.  However I'm willing to accept \dfS on the grounds of symmetry
with the behavior for tables.  I don't really want to put up with a less
convenient behavior *and* a gratuitously different syntax.
        regards, tom lane


Re: psql questions: SQL, progname, copyright dates

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I talked to Greg via chat and it looks like '&' is the best choice for
> > adding system object display:
> 
> >     \d& shows system stuff
> 
> Yech, that's awful.  It looks ugly and it commandeers a punctuation
> symbol that we might wish to use for something else someday.
> 
> I thought the "S" suggestion was much better than this.

My problem is that it uses a letter as a modifier, while all other
letters are object specifications.  '+' is a modifier.  We need another
modifier that isn't a letter.  No one knew \dtS worked because 'S'
doesn't look like a modifier.

> Personally I am not unhappy with the existing behavior, because (unlike
> Greg I guess) I use \df and \do to look at system definitions all the
> time.  However I'm willing to accept \dfS on the grounds of symmetry
> with the behavior for tables.  I don't really want to put up with a less
> convenient behavior *and* a gratuitously different syntax.

I am also happy with the existing behavior.  Another idea is to add a
modifier that supresses system functions, etc.

I think adding 'S' to \df confuses more than it helps.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: psql questions: SQL, progname, copyright dates

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> I thought the "S" suggestion was much better than this.

> My problem is that it uses a letter as a modifier, while all other
> letters are object specifications.  '+' is a modifier.  We need another
> modifier that isn't a letter.  No one knew \dtS worked because 'S'
> doesn't look like a modifier.

I don't buy that argument in the least.  I think the reason people
didn't know about "S" was they didn't RTFM (or possibly that the FM
isn't sufficiently clear).  Changing to a different character won't make
any difference at all, only improving the docs will make a difference.

But I could live with using "-" to suppress system objects.  That isn't
a character we're likely to want to use as a command metacharacter
someday.
        regards, tom lane


Re: psql questions: SQL, progname, copyright dates

From
Andrew Dunstan
Date:

Tom Lane wrote:

>Bruce Momjian <pgman@candle.pha.pa.us> writes:
>  
>
>>Tom Lane wrote:
>>    
>>
>>>I thought the "S" suggestion was much better than this.
>>>      
>>>
>
>  
>
>>My problem is that it uses a letter as a modifier, while all other
>>letters are object specifications.  '+' is a modifier.  We need another
>>modifier that isn't a letter.  No one knew \dtS worked because 'S'
>>doesn't look like a modifier.
>>    
>>
>
>I don't buy that argument in the least.  I think the reason people
>didn't know about "S" was they didn't RTFM (or possibly that the FM
>isn't sufficiently clear).  Changing to a different character won't make
>any difference at all, only improving the docs will make a difference.
>
>But I could live with using "-" to suppress system objects.  That isn't
>a character we're likely to want to use as a command metacharacter
>someday.
>
>
>  
>

ISTM one problem is we are inconsistent about it - \d  and \dt don't 
show system objects, but \df shows system functions. Reading TFM is a 
good thing, but so is consistency.

'-' isn't a very nice choice, because \df-+ would be really confusing. 
If you don't like '&', then '@' and '!' seem to be at least as free as 
'-' ;-)

cheers

andrew


Re: psql questions: SQL, progname, copyright dates

From
Tom Lane
Date:
Andrew Dunstan <andrew@dunslane.net> writes:
> ISTM one problem is we are inconsistent about it - \d  and \dt don't 
> show system objects, but \df shows system functions. Reading TFM is a 
> good thing, but so is consistency.

Well, one of the subarguments here is whether we are going to change the
behavior of the table-related \d commands too.  If we choose a modifier
other than S for \df, I'd be inclined to adopt the same behavior for the
table commands.

> '-' isn't a very nice choice, because \df-+ would be really confusing. 
> If you don't like '&', then '@' and '!' seem to be at least as free as 
> '-' ;-)

[ shrug ]  But '-' has the correct implication that you're removing
something.  Those other symbols are just arbitrary.  I'd like to pick
something with at least some mnemonic value.
        regards, tom lane


Re: psql questions: SQL, progname, copyright dates

From
Alvaro Herrera
Date:
On Thu, Sep 09, 2004 at 11:37:13AM -0000, Greg Sabino Mullane wrote:

> To clarify the 'S' a bit more, here is the output from \? in my new patch:
>  
> Informational (S = show system objects)
>   \l                list all databases (add "+" for more detail)
>   \d[S]             list tables, views, and sequences
>   \d[S] NAME        describe table, view, sequence, or index
>   \dt[S] [PATTERN]  list tables (add "+" for more detail)

While you are it, why not make it

> Informational
> (S = show system objects)
> (+ = show more detail about each object)
>   \l[+]                list all databases
>   \d[S]                list tables, views, and sequences
>   \d[S][+] NAME        describe table, view, sequence, or index
>   \dt[S][+] [PATTERN]  list tables

Etc?

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"No reniegues de lo que alguna vez creíste"



Re: psql questions: SQL, progname, copyright dates

From
Greg Stark
Date:
"Greg Sabino Mullane" <greg@turnstep.com> writes:

> To clarify the 'S' a bit more, here is the output from \? in my new patch:

This looks nice perhaps do it for "+" as well?

> Informational:
>   Modifiers
>      S  Show system objects
>      +  Additional detail
>   \l[+]              list all databases
>   \d[S]              list tables, views, and sequences
>   \d[S] NAME         describe table, view, sequence, or index
>   \dt[S+] [PATTERN]  list tables
>   \dv[S+] [PATTERN]  list views
>   \ds[S+] [PATTERN]  list sequences
>   \di[S+] [PATTERN]  list indexes
>   \df[S+] [PATTERN]  list functions
>   \dD[S]  [PATTERN]  list domains
>   \do[S]  [NAME]     list operators
>   \da[S]  [PATTERN]  list aggregate functions
>   \dT[S+] [PATTERN]  list data types
>   \dc[S]  [PATTERN]  list conversions
>   \db[+]  [PATTERN]  list tablespaces


-- 
greg



Re: psql questions: SQL, progname, copyright dates

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> While you are it, why not make it
> Informational
> (S = show system objects)
> (+ = show more detail about each object)
>   \l[+]                list all databases
>   \d[S]                list tables, views, and sequences
>   \d[S][+] NAME        describe table, view, sequence, or index
Very good idea. I guess I am once again in the 'S' camp, after a brief
foray into '&' land. Sorry, Bruce! :)
I'll keep working on the patch when I have the time.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200409091808
-----BEGIN PGP SIGNATURE-----
iD8DBQFBQNScvJuQZxSWSsgRAv5JAKC9uvu+SZ6vszLhe3sytjUby9cgRACfaW2v
BVJ2JcLzpJi8hp47tdh16gw=
=GrSh
-----END PGP SIGNATURE-----




Re: psql questions: SQL, progname, copyright dates

From
Bruce Momjian
Date:
Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
> > ISTM one problem is we are inconsistent about it - \d  and \dt don't 
> > show system objects, but \df shows system functions. Reading TFM is a 
> > good thing, but so is consistency.
> 
> Well, one of the subarguments here is whether we are going to change the
> behavior of the table-related \d commands too.  If we choose a modifier
> other than S for \df, I'd be inclined to adopt the same behavior for the
> table commands.
> 
> > '-' isn't a very nice choice, because \df-+ would be really confusing. 
> > If you don't like '&', then '@' and '!' seem to be at least as free as 
> > '-' ;-)
> 
> [ shrug ]  But '-' has the correct implication that you're removing
> something.  Those other symbols are just arbitrary.  I'd like to pick
> something with at least some mnemonic value.

One question is whether we want to suppress system functions by default
in \df.  From my perspective, the issue is whether we would use those
objects in normal application queries.  Clearly we would use system
functions in application queries, so we display them.  (We might be able
to suppress display of interally called functions if we still display
them).  We don't refer to system tables in normal application queries so
it makes sense we don't display them by default.

As far as the symbol, I think we should keep letters as object
specifiers and not use 'S' to mean system.  The idea for '&' was to say
"and system objects", and if we go for an options to supress system
objects, '-' seems best.  I don't imagine people using -+ very often.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: psql questions: SQL, progname, copyright dates

From
Robert Treat
Date:
On Thursday 09 September 2004 21:30, Bruce Momjian wrote:
> Tom Lane wrote:
> > Andrew Dunstan <andrew@dunslane.net> writes:
> > > ISTM one problem is we are inconsistent about it - \d  and \dt don't
> > > show system objects, but \df shows system functions. Reading TFM is a
> > > good thing, but so is consistency.
> >
> > Well, one of the subarguments here is whether we are going to change the
> > behavior of the table-related \d commands too.  If we choose a modifier
> > other than S for \df, I'd be inclined to adopt the same behavior for the
> > table commands.
> >
> > > '-' isn't a very nice choice, because \df-+ would be really confusing.
> > > If you don't like '&', then '@' and '!' seem to be at least as free as
> > > '-' ;-)
> >
> > [ shrug ]  But '-' has the correct implication that you're removing
> > something.  Those other symbols are just arbitrary.  I'd like to pick
> > something with at least some mnemonic value.
>
> One question is whether we want to suppress system functions by default
> in \df.  From my perspective, the issue is whether we would use those
> objects in normal application queries.  Clearly we would use system
> functions in application queries, so we display them.  (We might be able
> to suppress display of interally called functions if we still display
> them).  We don't refer to system tables in normal application queries so
> it makes sense we don't display them by default.

FWIW in phppgadmin we do suppress system functions by default and I can't 
recall anyone ever asking us to do otherwise.  Its actually one of the 
reasons I use phppgadmin, because when trying to work through complex 
function code, having all of the system functions is just to cumbersome.  

>
> As far as the symbol, I think we should keep letters as object
> specifiers and not use 'S' to mean system.  The idea for '&' was to say
> "and system objects", and if we go for an options to supress system
> objects, '-' seems best.  I don't imagine people using -+ very often.
>

Ugh. If I want to see the syntax of my functions, I'd be forced to use the 
\df-+ syntax, and I'd argue people spend far more time wanting to see \df+ 
output on their own functions than they ever do on system functions. 

imho the argument against \dfS is pretty weak.  Letters are not only used as 
object specifiers, they are also used for setting field separators, html 
output, switching to and from expanded output, and listing table access 
permissions, among other things.  Telling folks that the S modifier is for 
system objects mnemonic, simple, and FWIW keeps with backward compatability.

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


Re: psql questions: SQL, progname, copyright dates

From
"Zeugswetter Andreas SB SD"
Date:
> I think adding 'S' to \df confuses more than it helps.

Why that? Imho it would be consistent.

Andreas


Re: psql questions: SQL, progname, copyright dates

From
Gaetano Mendola
Date:
Robert Treat wrote:

> Ugh. If I want to see the syntax of my functions, I'd be forced to use the 
> \df-+ syntax, and I'd argue people spend far more time wanting to see \df+ 
> output on their own functions than they ever do on system functions. 
> 
> imho the argument against \dfS is pretty weak.  Letters are not only used as 
> object specifiers, they are also used for setting field separators, html 
> output, switching to and from expanded output, and listing table access 
> permissions, among other things.  Telling folks that the S modifier is for 
> system objects mnemonic, simple, and FWIW keeps with backward compatability.

One thing is emerging from this thread: the \xyzk functionality is overloaded.
Time to switch to something more human readable ?



Regards
Gaetano Mendola






Re: psql questions: SQL, progname, copyright dates

From
Bruce Momjian
Date:
Zeugswetter Andreas SB SD wrote:
> 
> > I think adding 'S' to \df confuses more than it helps.
> 
> Why that? Imho it would be consistent.

I thought it was strange to have alphabetic modifiers but I seem to be
the only one who is worried about it so forget my objection.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: psql questions: SQL, progname, copyright dates

From
Andrew Dunstan
Date:

Robert Treat wrote:

>Ugh. If I want to see the syntax of my functions, I'd be forced to use the 
>\df-+ syntax, and I'd argue people spend far more time wanting to see \df+ 
>output on their own functions than they ever do on system functions. 
>
>  
>

+1. I suspect Tom's use is pretty atypical. If I want to see a system 
function I usually name it. Otherwise I find myself doing \df public.*, 
which is usually after I have forgotten that that's what I need to do.

cheers

andrew