Re: Re: [INTERFACES] remote database queries
От | Joe Conway |
---|---|
Тема | Re: Re: [INTERFACES] remote database queries |
Дата | |
Msg-id | 002901c0e7ea$d7ac9830$0705a8c0@jecw2k1 обсуждение исходный текст |
Ответ на | Re: [INTERFACES] remote database queries (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
> [ 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
В списке pgsql-hackers по дате отправления: