Passing NULL values in dblink function call - Mailing list pgsql-general

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 (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(a,b,c,d);
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 (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.

Thanks and Regards,

Piyush Jaisingkar ,

App. Software Dev. Sr. App. Conslt. | NTT DATA GDS, Pune.

Contact : m: +91.9405320360 | Voip +88348.149 | piyush.jaisingkar@nttdata.com

NTT DATA Global Delivery Services Private Limited Consulting | Digital | Managed Services | Industry Solutions

Learn more: