Message-ID: <002901c0e7ea$d7ac9830$0705a8c0@jecw2k1>
From: "Joe Conway" <joe@conway-family.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-hackers@postgresql.org>
References: <Pine.LNX.4.30.0105271914200.14985-100000@commandprompt.com>
	<002901c0e733$5e683590$0705a8c0@jecw2k1>
	<15650.991084313@sss.pgh.pa.us>
Subject: Re: Re: [INTERFACES] remote database queries 
Date: Mon, 28 May 2001 19:55:37 -0700
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

> [ Redirected away from the entirely inappropriate pgsql-interfaces list ]

oops . . . sorry!

> Until we fix that (maybe for 7.2, maybe not) your existing hack is
> probably pretty reasonable.  You could save some cycles by avoiding
> conversion to text, though --- instead return an opaque datum that is
> pointer-to-tuple-slot and let the dblink_tok function extract fields
> from the tuple.  Look at SQL function support and the FieldSelect
> expression node type for inspiration.

Thanks -- I'll take a look.


> I think this would work if the planner weren't so enthusiastic about
> trying to collapse the sub-SELECT query together with the main query.
> Unfortunately it doesn't check to see if any set-valued functions are
> involved before it collapses 'em --- leaving you with a set-valued
> function call in the WHERE clause.  Not sure if this is worth fixing,
> considering that using set-valued functions in this way is just a
> band-aid that doesn't have a long life expectancy.

I'd certainly bow to your wisdom in this area, but I was thinking it would
be useful (at times) to force a FROM clause sub-select to be treated as if
it were a "real" table (probably not the best way to express this, but
hopefully you get the idea). In MSSQL I've found many situations where
putting intermediate results into a temp table, and then joining to it, is
significantly faster than letting the optimizer do it's best. But the fact
that MSSQL will return record sets from a stored procedure help makes this
tolerable/manageable -- i.e. the whole ugly mess can be rolled into one nice
neat strored procedure call. If the FROM clause sub-select could be treated,
in a sense, like an on-the-fly temp table in PostgreSQL, a similar result is
possible. And if the whole ugly mess is rolled up behind a view, no one has
to know except the especially curious ;-)

>
> If you just need a hack with a short life expectancy, here's a hack that
> I recommend not reading right before dinner ... might make you lose your
> appetite ;-).  Build the view as a dummy UNION:

*Very* few things make me lose my appetite -- and this worked perfectly!
Thank you.

-- Joe