Re: [HACKERS] [GENERAL] Using Cursor in PostgreSQL 7.2

Поиск
Список
Период
Сортировка
Искать

Re: [HACKERS] [GENERAL] Using Cursor in PostgreSQL 7.2

От:
Bruce Momjian <pgman@candle.pha.pa.us>
Дата:

Patch applied.  Thanks.

---------------------------------------------------------------------------


> Tom Lane wrote:
> 
> > Joe Conway  writes:
> > 
> >>I confirmed the UNION hack is no longer required. Thanks! Is it too late 
> >>to change the README in contrib/dblink?
> >>
> > 
> > No, I don't think that's a problem.  Send a patch.
> > 
> 
> Here's a (documentation only) patch for the contrib/dblink README.
> 
> Thanks,
> 
> Joe

> *** README.dblink.orig	Mon Jun 18 12:09:50 2001
> --- README.dblink	Wed Dec 12 19:57:34 2001
> ***************
> *** 82,88 ****

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: [HACKERS] [GENERAL] Using Cursor in PostgreSQL 7.2

От:
Joe Conway <joseph.conway@home.com>
Дата:
Tom Lane wrote:

> Joe Conway  writes:
> 
>>I confirmed the UNION hack is no longer required. Thanks! Is it too late 
>>to change the README in contrib/dblink?
>>
> 
> No, I don't think that's a problem.  Send a patch.
> 

Here's a (documentation only) patch for the contrib/dblink README.

Thanks,

Joe
*** README.dblink.orig	Mon Jun 18 12:09:50 2001
--- README.dblink	Wed Dec 12 19:57:34 2001
***************
*** 82,88 ****
  
  Example usage
  
!   select dblink('hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd','select f1, f2 from mytable');
  
  
  ==================================================================
--- 82,89 ----
  
  Example usage
  
!   select dblink('hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'
!                ,'select f1, f2 from mytable');
  
  
  ==================================================================
***************
*** 118,161 ****
  
  ==================================================================
  
! NOTE: If you need to provide selection criteria in a WHERE clause, it is necessary
! to 'fake' a UNION, e.g.
  
!   select
!      dblink_tok(t1.dblink_p,0) as f1
!     ,dblink_tok(t1.dblink_p,1) as f2
!   from
!    (
!     select dblink(
!                    'hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'
!                   ,'select f1, f2 from mytable'
!                  ) as dblink_p
!     union all
!     select null where false
!    ) as t1
!   where
!     dblink_tok(t1.dblink_p,0) = 'mycriteria';
! 
! in order to work around an issue with the query optimizer. A more convenient way to approach
! this problem is to create a view:
! 
!   create view myremotetable as
!   select
!      dblink_tok(t1.dblink_p,0) as f1
!     ,dblink_tok(t1.dblink_p,1) as f2
!   from
!    (
!     select dblink(
!                    'hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd'
!                   ,'select f1, f2 from mytable'
!                  ) as dblink_p
!     union all
!     select null where false
!    ) as t1;
  
  Then you can simply write:
  
!    select f1,f2 from myremotetable where f1 = 'mycriteria';
  
  ==================================================================
  
--- 119,134 ----
  
  ==================================================================
  
! A more convenient way to use dblink may be to create a view:
  
!  create view myremotetable as
!  select dblink_tok(t1.dblink_p,0) as f1, dblink_tok(t1.dblink_p,1) as f2
!  from (select dblink('hostaddr=127.0.0.1 port=5432 dbname=template1 user=postgres password=postgres'
!                     ,'select proname, prosrc from pg_proc') as dblink_p) as t1;
  
  Then you can simply write:
  
!    select f1, f2 from myremotetable where f1 like 'bytea%';
  
  ==================================================================
FAQ