As you might recognize, this is supposed to be a psql \d imitation in one 
shot:

SELECT usename as "Owner", relname as "Relation",
(CASE WHEN relkind='r' THEN 
	(CASE WHEN 0<(select count(*) from pg_views where viewname = relname)
	THEN 'view' ELSE 'table' END)      
      WHEN relkind='i' THEN 'index'
      WHEN relkind='S' THEN 'sequence'
      ELSE 'other'
END) as "Type"
FROM pg_class, pg_user
WHERE usesysid = relowner AND
( relkind = 'r' OR
  relkind = 'i' OR
  relkind = 'S') AND
relname !~ '^pg_' AND
(relkind != 'i' OR relname !~ '^xinx')  ORDER BY relname;
ERROR:  flatten_tlistentry: Cannot handle node type 108

However, if you do 
-	(CASE WHEN 0<(select count(*) from pg_views where viewname = relname)
-	THEN 'view' ELSE 'table' END)      
+	'relation'
if works fine. No nested CASE's?

PostgreSQL 6.5.2 on i586-pc-linux-gnu, compiled by egcs

-- 
Peter Eisentraut - peter_e@gmx.net
http://yi.org/peter-e


Fri, 1 Oct 1999 03:03:46 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Tricky query, tricky response
Peter Eisentraut <peter_e@gmx.net> writes:
> However, if you do 
> -	(CASE WHEN 0<(select count(*) from pg_views where viewname = relname)
> -	THEN 'view' ELSE 'table' END)      
> +	'relation'
> if works fine. No nested CASE's?

Nope: no sub-selects in target list.

I'm hoping to fix that soon, but if you want psql to continue to work
with pre-6.6 backends then you'll have to use a different approach.

			regards, tom lane

Sat, 02 Oct 1999 11:00:23 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Tricky query, tricky response 
> No nested CASE's?

Looks like not. I would guess that it is fairly straightforward to
fix, but am not sure. Tom Lane hunted down an killed most of the CASE
problems (thanks Tom!), and this is in an area he is working on now.
Maybe you can get him to look at it??

                     - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Sat, 02 Oct 1999 15:00:59 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] Tricky query, tricky response
On Oct 2, Tom Lane mentioned:

> Nope: no sub-selects in target list.
> 
> I'm hoping to fix that soon, but if you want psql to continue to work
> with pre-6.6 backends then you'll have to use a different approach.

Question number one is: Do I? Yeah, okay :)

Anyway, I thought wouldn't a more, um, user-friendly error message like
ERROR: Subselects are not allowed in target list.
be more desirable than
ERROR:  flatten_tlistentry: Cannot handle node type 108

If _I_ read the latter I can at least suspect that there is a problem in
the query tree, but Joe User that just learned about inodes the other day
is going to think his system is broken is all sorts of ways.

Another example is
FATAL 1:  SetUserId: user 'joeschmoe' is not in 'pg_shadow'
clearly not as nice as
FATAL ERROR: 'joeschmoe' is not a valid database user.

(Also, if you want to be really secure you wouldn't give them the
information that 'joeschmoe' is not a valid user but rather just return
"Permission denied" or "Authentication failed". -- cf. login(1) )

I think that ought to be a TODO item, right above
* Allow international error message support and add error codes
Perhaps it's even the same in essence.

-- 
Peter Eisentraut - peter_e@gmx.net
http://yi.org/peter-e


Sun, 3 Oct 1999 07:31:11 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] Tricky query, tricky response 
Peter Eisentraut <peter_e@gmx.net> writes:
> Anyway, I thought wouldn't a more, um, user-friendly error message like
> ERROR: Subselects are not allowed in target list.
> be more desirable than
> ERROR:  flatten_tlistentry: Cannot handle node type 108

Yes, it would.  Are you volunteering to try to make that happen?
(Not for this one case, but for everything?)

There's been some discussion of trying to clean up the error reporting
conventions, and in particular separate internal details (such as which
routine is reporting the error) from the "user level" information.
But a lot of the internal checks are pretty content-free from a user's
point of view, and there's little to be done about that.  (Does
flatten_tlistentry have a clue *why* it got a node type it never heard
of?  Nyet.)  I do not think that a generic "Internal error" message
would be an improvement over what we have, messy though it is.  It's
not a simple problem...

			regards, tom lane

Sun, 03 Oct 1999 12:53:18 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Tricky query, tricky response 
On Oct 3, Tom Lane mentioned:

> Peter Eisentraut <peter_e@gmx.net> writes:
> > Anyway, I thought wouldn't a more, um, user-friendly error message like
> > ERROR: Subselects are not allowed in target list.
> > be more desirable than
> > ERROR:  flatten_tlistentry: Cannot handle node type 108
> 
> Yes, it would.  Are you volunteering to try to make that happen?

Hmm, I'll put it on the List O' Things to consider right after I clear out
all the crap that has accumulated in psql over the years which will take
an unpredictable amount of time still.


-- 
Peter Eisentraut - peter_e@gmx.net
http://yi.org/peter-e/


Tue, 5 Oct 1999 22:30:35 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Error messages (was Re: [HACKERS] Tricky query, tricky response)
> > No nested CASE's?
> 
> Looks like not. I would guess that it is fairly straightforward to
> fix, but am not sure. Tom Lane hunted down an killed most of the CASE
> problems (thanks Tom!), and this is in an area he is working on now.
> Maybe you can get him to look at it??
> 

Is this an item for the TODO list?


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 29 Nov 1999 18:27:34 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] Tricky query, tricky response
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>>>> No nested CASE's?
>> 
>> Looks like not. I would guess that it is fairly straightforward to
>> fix, but am not sure. Tom Lane hunted down an killed most of the CASE
>> problems (thanks Tom!), and this is in an area he is working on now.
>> Maybe you can get him to look at it??

> Is this an item for the TODO list?

Fixed in current sources, I believe.

			regards, tom lane

Mon, 29 Nov 1999 21:30:58 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Tricky query, tricky response 
On 1999-11-29, Tom Lane mentioned:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >>>> No nested CASE's?
> >> 
> >> Looks like not. I would guess that it is fairly straightforward to
> >> fix, but am not sure. Tom Lane hunted down an killed most of the CASE
> >> problems (thanks Tom!), and this is in an area he is working on now.
> >> Maybe you can get him to look at it??
> 
> > Is this an item for the TODO list?
> 
> Fixed in current sources, I believe.

The problem (I sent it in) was actually no sub-selects in target list.
Nested cases work fine I believe.

-- 
Peter Eisentraut                  Sernanders vï¿½g 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden


Wed, 1 Dec 1999 01:27:30 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] Tricky query, tricky response 
Peter Eisentraut <peter_e@gmx.net> writes:
>>>> Is this an item for the TODO list?
>> 
>> Fixed in current sources, I believe.

> The problem (I sent it in) was actually no sub-selects in target list.

Still fixed in current sources ;-) ...

			regards, tom lane

Wed, 01 Dec 1999 00:17:51 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Tricky query, tricky response 