Foreign PostgreSQL server

Hi!

Sorry if this was already asked.

My platform: Win7, PostgreSQL 9.1 64bit installed using EnterpriseDB
package.

I’m trying to setup a bunch of foreign PostgreSQL servers, but whenever I
execute the script below, I get errors:

----------------------------------------------------------------------------
--
drop foreign data wrapper postgresql_fdw cascade;

create extension dblink;

CREATE FOREIGN DATA WRAPPER postgresql_fdw
VALIDATOR postgresql_fdw_validator
HANDLER postgresql_fdw_handler;

CREATE SERVER simfrete02 FOREIGN DATA WRAPPER pgsql OPTIONS (host
'127.0.0.1', dbname 'simfrete', port '5432');
----------------------------------------------------------------------------
---
And the errors are:
ERROR: function postgresql_fdw_handler() does not exists

********** Error **********

ERROR: function postgresql_fdw_handler() does not exists
SQL state: 42883

Can you point what I'm doing wrong?

Thanks & Regards,

Edson Carlos Ericksson Richter
SimKorp Infomática Ltda
Fone:
(51) 3366-7964
Celular:
(51) 8585-0796

www.simkorp.com.br

Ok, managed to get the first part working:

---------------------------------------------------------------------------
create extension dblink;
CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator;
CREATE SERVER simfrete02 FOREIGN DATA WRAPPER postgresql OPTIONS (host
'127.0.0.1', dbname 'simfrete', port '5432');
CREATE FOREIGN TABLE emp02 (ID integer NOT NULL, NOME varchar(100) NOT NULL)
SERVER simfrete02;
select * from usuario join emp02 on usuario.empresa_id = emp02.id;
---------------------------------------------------------------------------
This error relates to the "select ..." part of the script above:

ERRO: foreign-data wrapper "postgresql" has no handler
********** Error **********
ERRO: foreign-data wrapper "postgresql" has no handler
SQL state: 55000

If I remove the "select ...", then the script runs without any error.

What then?

Thanks for your support,

Edson Richter

On Friday, September 16, 2011 4:03:03 pm Edson Carlos Ericksson Richter wrote:
> Ok, managed to get the first part working:
>
> ---------------------------------------------------------------------------
> create extension dblink;
> CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator;
> CREATE SERVER simfrete02 FOREIGN DATA WRAPPER postgresql OPTIONS (host
> '127.0.0.1', dbname 'simfrete', port '5432');
> CREATE FOREIGN TABLE emp02 (ID integer NOT NULL, NOME varchar(100) NOT
> NULL) SERVER simfrete02;
> select * from usuario join emp02 on usuario.empresa_id = emp02.id;
> ---------------------------------------------------------------------------
> This error relates to the "select ..." part of the script above:
>
> ERRO: foreign-data wrapper "postgresql" has no handler
> ********** Error **********
> ERRO: foreign-data wrapper "postgresql" has no handler
> SQL state: 55000

You did not specify a handler for the FDW postgresql and per the Docs:

http://www.postgresql.org/docs/9.1/interactive/sql-createforeigndatawrapper.html

"It is possible to create a foreign-data wrapper with no handler function, but
foreign tables using such a wrapper can only be declared, not accessed. "

>
>
> If I remove the "select ...", then the script runs without any error.
>
> What then?
>
>
> Thanks for your support,
>
> Edson Richter

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

Thanks Adrian, I really miss that part...
Anyway, if I try to declare a handler like

CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator
HANDLER postgresql_fdw_handler;

(as found on Wiki) I get error

“ERROR: function postgresql_fdw_handler() does not exists”

I’m definitely missing something...

What is the way to go?

Edson.

De: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] Em nome de Adrian Klaver
Enviada em: sexta-feira, 16 de setembro de 2011 23:30
Para: pgsql-general(at)postgresql(dot)org
Cc: Edson Carlos Ericksson Richter
Assunto: Re: RES: [GENERAL] Foreign PostgreSQL server

On Friday, September 16, 2011 4:03:03 pm Edson Carlos Ericksson Richter
wrote:
> Ok, managed to get the first part working:
>
>
---------------------------------------------------------------------------
> create extension dblink;
> CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator;
> CREATE SERVER simfrete02 FOREIGN DATA WRAPPER postgresql OPTIONS (host
> '127.0.0.1', dbname 'simfrete', port '5432');
> CREATE FOREIGN TABLE emp02 (ID integer NOT NULL, NOME varchar(100) NOT
> NULL) SERVER simfrete02;
> select * from usuario join emp02 on usuario.empresa_id = emp02.id;
>
---------------------------------------------------------------------------
> This error relates to the "select ..." part of the script above:
>
> ERRO:  foreign-data wrapper "postgresql" has no handler
> ********** Error **********
> ERRO: foreign-data wrapper "postgresql" has no handler
> SQL state: 55000

You did not specify a handler for the FDW postgresql and per the Docs:

http://www.postgresql.org/docs/9.1/interactive/sql-createforeigndatawrapper.
html

"It is possible to create a foreign-data wrapper with no handler function,
but
foreign tables using such a wrapper can only be declared, not accessed. "

>
>
> If I remove the "select ...", then the script runs without any error.
>
> What then?
>
>
> Thanks for your support,
>
> Edson Richter

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

________________________________________
avast! Antivirus: Inbound message clean.
Virus Database (VPS): 17/09/2011
Tested on: 17/09/2011 09:15:24
avast! - copyright (c) 1988-2011 AVAST Software.

On Saturday, September 17, 2011 5:22:35 am Edson Carlos Ericksson Richter wrote:
> Thanks Adrian, I really miss that part...
> Anyway, if I try to declare a handler like
>
> CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator
> HANDLER postgresql_fdw_handler;
>
> (as found on Wiki) I get error
>
> “ERROR: function postgresql_fdw_handler() does not exists”
>
> I’m definitely missing something...
>
> What is the way to go?

The closest I can come to an answer is this link:

http://grokbase.com/t/postgresql.org/pgsql-general/2011/06/foreign-table-with-
dblink/16pmfkdiltzchnj4dl5cmnk7xqc4

It seems to indicate postgresql_fdw did not make it into 9.1. Others may be able
to shed more light on this.

>
> Edson.
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

Thanks, Adrian!

That was the piece of information I was missing.

I'll wait, and hopefully this will be released as an external module.

Kind regards,

Edson.

-----Mensagem original-----
De: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] Em nome de Adrian Klaver
Enviada em: sábado, 17 de setembro de 2011 14:30
Para: pgsql-general(at)postgresql(dot)org
Cc: Edson Carlos Ericksson Richter
Assunto: Re: RES: RES: [GENERAL] Foreign PostgreSQL server

On Saturday, September 17, 2011 5:22:35 am Edson Carlos Ericksson Richter
wrote:
> Thanks Adrian, I really miss that part...
> Anyway, if I try to declare a handler like
>
> CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR
> postgresql_fdw_validator HANDLER postgresql_fdw_handler;
>
> (as found on Wiki) I get error
>
> “ERROR: function postgresql_fdw_handler() does not exists”
>
> I’m definitely missing something...
>
> What is the way to go?

The closest I can come to an answer is this link:

http://grokbase.com/t/postgresql.org/pgsql-general/2011/06/foreign-table-wit
h-
dblink/16pmfkdiltzchnj4dl5cmnk7xqc4

It seems to indicate postgresql_fdw did not make it into 9.1. Others may be
able to shed more light on this.

>
> Edson.
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general