Re: SHOW TABLES

Hi,

Le 18 juil. 2010 à 05:41, Robert Haas a écrit :
> On Sat, Jul 17, 2010 at 11:14 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> I am concerned that implementing a command syntax to show complex output
>> like above effectively means re-implementing a subset of SQL, and that
>> subset will never be as flexible.
>
> That's a reasonable concern, but I don't have a better idea. Do you?

I think that SHOW could be some syntax sugar atop the current rewrite rules system. I mean it would be implemented by means of "parametrized" views. It could be that SQL only SRFs could do a better job at it. In both cases the idea is that we should be able to write SELECT like statements.

SHOW TABLE foo;

SHOW TABLES WHERE tablename ~ 'foo';

SHOW ANY TABLE
GROUP BY tablename
HAVING array_agg(attributes) @> array['date'::regtype, 'time'::regtype];

The last one has an "english like" trick using ANY rather than ALL, but that's just for the bikesheding of it, and would list all tables with both a date and a time column. The trick is there because if you want the attributes to show up you're after enhancing the SHOW TABLE query, not the SHOW TABLES one.

So what we'd need first is a series of named queries, which I think psql provides for. Then some technique to have them available both as plain and easy usage and in full SQL. I think the rewrite system is meant to allow that, I'm not sure if using views or pure SQL SRFs is better, in both cases the rewritten query has to provide arguments "placeholders": if a VIEW, that's a WHERE clause, if a SRF, any number of named arguments.

Regards,
--
Dimitri Fontaine
PostgreSQL DBA, Architecte