Postgres Pro
Downloads
Home   >   mailing lists

Thread: View using dblink fails if not able to make connection

View using dblink fails if not able to make connection

From
"George Weaver"
Date:
Hi Everyone,
 
I have a view made up of a local query unioned with a view comprised of a dblink query.
 
If the dblink query cannot establish a connection, I get the "could not connect to server" error and the whole view fails.
 
Setting fail_on_error to false does not work in this situation.
 
Is there any way to test the connection inside the view when the view is called and either return the whole view or just the local component if a dblink connection cannot be made?   
 
Thanks,
George
 
 
 

Re: View using dblink fails if not able to make connection

From
Igor Romanchenko
Date:


On Fri, Nov 9, 2012 at 9:21 PM, George Weaver <gweaver@shaw.ca> wrote:
Hi Everyone,
 
I have a view made up of a local query unioned with a view comprised of a dblink query.
 
If the dblink query cannot establish a connection, I get the "could not connect to server" error and the whole view fails.
 
Setting fail_on_error to false does not work in this situation.
 
Is there any way to test the connection inside the view when the view is called and either return the whole view or just the local component if a dblink connection cannot be made?   
 
Thanks,
George
 
 
 
Hi.
You can put you dblink query into PL/pgSQL function that:
1) Will return dblink result, if no errors occurred.
2) Will return no rows, if error occured

Then just use this function in the union.

Re: View using dblink fails if not able to make connection

From
"George Weaver"
Date:

----- Original Message -----
From: Igor Romanchenko

>On Fri, Nov 9, 2012 at 9:21 PM, George Weaver <gweaver@shaw.ca> wrote:

>>Hi Everyone,

>>I have a view made up of a local query unioned with a view comprised of a dblink query.

>>If the dblink query cannot establish a connection, I get the "could not connect to server" error and the whole view fails.

>>Setting fail_on_error to false does not work in this situation.

>>Is there any way to test the connection inside the view when the view is called and either return the whole view or just the local component if a dblink connection cannot be made?   

>>Thanks,
>>George

>Hi.
>You can put you dblink query into PL/pgSQL function that:
>1) Will return dblink result, if no errors occurred.
>2) Will return no rows, if error occured

>Then just use this function in the union.
 
Hi Igor,
 
I'll look at that!
 
Thanks,
George