Thread: Tricky query, tricky response
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
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
> 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
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
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
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/
> > 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, Pennsylvania19026
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
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
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