Thread: BUG #6708: pgsql_fdw's foreign table cann't used in plpgsql function

The following bug has been logged on the website:

Bug reference:      6708
Logged by:          digoal.zhou
Email address:      digoal@126.com
PostgreSQL version: 9.1.3
Operating system:   CentOS 5.x bit64
Description:=20=20=20=20=20=20=20=20

pgsql_fdw's foreign table "cache lookup failed for type 0" bug when used in
function
1.INSTALL
https://github.com/dvarrazzo/interdbconnect/downloads
mv pgsql_fdw $PG_SRC/contrib/
cd $PG_SRC/contrib/pgsql_fdw
. /home/postgres/.bash_profile
USE_PGXS=3D1 make
USE_PGXS=3D1 make install
2.TEST
digoal=3D# create extension pgsql_fdw;
CREATE EXTENSION

CREATE SERVER srv_digoal FOREIGN DATA WRAPPER pgsql_fdw
OPTIONS (host '172.16.3.33', port '5432', dbname 'digoal');

CREATE USER MAPPING FOR digoal SERVER srv_digoal
OPTIONS (user 'rmt_digoal', password 'DIGOAL123');

grant usage on foreign server srv_digoal to digoal;

\c digoal digoal
CREATE FOREIGN TABLE test
(
id int
) server srv_digoal options (nspname 'digoal', relname 'table_digoal');

select * from test;
It can do success, and return currect rows;

but , if test table in plpgsql function , then error occur:
create or replace function f_test () returns int as $$
declare
begin
perform 1 from test limit 1;
return 0;
end;
$$ language plpgsql;


digoal=3D# \set VERBOSITY verbose
digoal=3D# select * from f_test();
ERROR:  XX000: cache lookup failed for type 0
CONTEXT:  SQL statement "SELECT 1 from test limit 1"
PL/pgSQL function "f_test" line 3 at PERFORM
LOCATION:  getTypeOutputInfo, lsyscache.c:2440

3. DETAIL information please visit my blog :=20
http://blog.163.com/digoal@126/blog/static/16387704020125218171919/

thanks very much;

(2012/06/27 9:02), digoal@126.com wrote:
> pgsql_fdw's foreign table "cache lookup failed for type 0" bug when used in
> function

Thanks for the report!

I could reproduce the problem.  The cause of error was wrong handling of
parameters given by PL/pgSQL for PL/pgSQL variables and parameters.

They need instantiation with callback routine, and might have InvalidOid
(0) for type oid.  I fixed pgsql_fdw for two points:

1) instantiate parameters given by PL/pgSQL with callback function
2) use TEXTOID for type-unknown parameters which are not used in remote
query

Please try HEAD of git.
    https://sourceforge.net/projects/interdbconnect/develop

Regards,
--
Shigeru HANADA