Re: SHOW TABLES

On Mon, Jul 19, 2010 at 09:31:06AM -0500, Kevin Grittner wrote:
> >Stephen Frost <sfrost(at)snowman(dot)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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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