Обсуждение: Foreign PostgreSQL server
Hi!
Sorry if this was already asked.
My platform: Win7, PostgreSQL 9.1 64bit installed using EnterpriseDB
package.
Im 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.klaver@gmail.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 Im definitely missing something... What is the way to go? Edson. De: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] Em nome de Adrian Klaver Enviada em: sexta-feira, 16 de setembro de 2011 23:30 Para: pgsql-general@postgresql.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.klaver@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.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 > > Im 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.klaver@gmail.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@postgresql.org [mailto:pgsql-general-owner@postgresql.org] Em nome de Adrian Klaver Enviada em: sábado, 17 de setembro de 2011 14:30 Para: pgsql-general@postgresql.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 > > Im 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.klaver@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general