Re: SHOW TABLES

On Mon, Jul 19, 2010 at 09:31:06AM -0500, Kevin Grittner wrote:
> >Stephen Frost <sfrost@snowman.net> wrote:
>  
> > You think that the users of the libpq() interface (or even the
> > protocol itself) are going to handle getting \dt-type output back
> > somehow..?
>  
> If you look back in the thread, you'll see that I admitted my
> ignorance of whether this could be properly implemented in the back
> end without a protocol change.  Ignorance being bliss, I can revel
> in the dreams of *having* such a feature without being dragged down
> by the potential pain of its implementation.  ;-)
>  
> I know, though, that the JDBC spec supports such things -- you can
> keep pulling ResultSet objects off the wire, each with its own
> distinct set of columns.  (That is, each ResultSet has its own
> ResultSetMetaData which specifies how many columns that particular
> ResultSet has, what the column names are, what the data type is for
> each column, etc.  Each ResultSet returned from a response stream
> for a request can be entirely different in all of these
> characteristics.)

Would something like this do?  Thanks to Andrew Gierth for helping me
figure out how to get this working :)

CREATE OR REPLACE FUNCTION multi_result()
RETURNS SETOF REFCURSOR
LANGUAGE plpgsql
AS $$
DECLARE   r RECORD;   ref REFCURSOR;
BEGIN   FOR r IN       SELECT table_name       FROM information_schema.tables       WHERE table_schema =
'information_schema'  LOOP       ref := 'multi_result_' || quote_ident(r.table_name);       OPEN ref FOR EXECUTE
'SELECT* FROM information_schema.' || quote_ident(r.table_name); /* Not really needed. */       RETURN NEXT ref;
ref:= NULL;   END LOOP;   RETURN;
 
END;
$$;

BEGIN;
SELECT * FROM multi_result();
FETCH FORWARD ALL FROM multi_result_views;
ROLLBACK;

> > As what, a single-column result of type text?
>  
> No, that would be horrible.  That has been mentioned as a

+1 on the shuddering.  Add also nausea. :P

> > And then they'll use non-fixed-width fonts, undoubtably, which
> > means the results will end up looking rather ugly, even if we put
> > in the effort to format the results.
>  
> With, for example, Sybase's sp_help, each result set can be listed
> any way the client chooses -- I've seen it put into character format
> like the psql \d commands, I've seen each result set put into a
> table for brower-based query tools, and I've seen each result set
> put into a JTable for Java Swing applications.  If a client gets
> back a series of result sets, the sky is the limit.

Ad astra per PostgreSQL!

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
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate