Hello,
I have created two functions on two different databases connected them using dblink.
FOLLOWING IS THE LOCAL FUNCTION:
CREATE OR REPLACE FUNCTION chdb.dblink_onlocal() RETURNS table(par1 varchar,par2 varchar,par3 varchar) AS $$
DECLARE
query varchar;
r record;
BEGIN
PERFORM dblink_connect_u('codblink', 'host=xx.xx.xx.xx port=5432 dbname=smart_db user=postgres password=postgres');
RAISE WARNING 'Inside local function: Connected to dblink';
FOR r IN SELECT chdb.temp1.a::varchar,b,c,d from chdb.temp1 limit 10
LOOP
RAISE WARNING 'Inside for of local function: Connected to dblink';
IF(r.b is NULL and r.d is not null) THEN
query:=format('select arg1,arg2,arg3 from codb.dblink_function(''%s'',''%s'',''%s'',''%s'')',r.a,NULL::varchar,r.c,r.d);
END IF;
RETURN QUERY select * from dblink('codblink',query) as temp_addressbase_feed(parg1,parg2,parg3);
END LOOP;
PERFORM dblink_disconnect('codblink');
END;
$$ LANGUAGE plpgsql;
FOLLOWING IS THE REMOTE FUNCTION:
CREATE OR REPLACE FUNCTION codb.dblink_function(a varchar, b varchar, c text, d varchar)
RETURNS table(par1 varchar, par2 varchar, par3 varchar) as
$$
DECLARE
BEGIN
IF(b is not null)
THEN
RETURN QUERY SELECT a,caf.longitude::real,caf.latitude::real FROM codb.address_feed as caf limit 1 ;
ELSIF(d is not null)
THEN
RETURN QUERY SELECT 'aid2'::varchar,caf1.longitude::real,caf1.latitude::real FROM codb.address_feed as caf1 where caf1.postcode_locator= d;
ElSE
RAISE WARNING 'Inside ELSE: value of a=%,b=%, c=%, d=%',a,b,c,d;
RETURN QUERY SELECT 'aid3'::varchar,0,0 FROM codb.address_feed as caf ;
END IF;
END
$$
LANGUAGE plpgsql;
All the values in the temp1 table column 'b' are NULL the condition on the local function is being satisfied and a NULL string is passed to the remote function where this parameter 'b' is checked
for being not null, here it passes even though we promptly sent a NULL.
To check whether this problem persists because of dblink or not, I created both this function on single database and made the function calls without using dblink, now the condition worked as
expected.
Can anyone tell me why a NULL is not passed and checked properly on remote side when using dblink.
Note: This code is a dummy, changes various names for security purpose.
Please help soon. Thanks in advance.
