Passing NULL values in dblink function call

["Jaisingkar, Piyush" <Piyush.Jaisingkar@xxxxxxxxxxx>]

 

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@xxxxxxxxxxx

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

Learn more:

 

 

______________________________________________________________________
Disclaimer: This email and any attachments are sent in strictest confidence
for the sole use of the addressee and may contain legally privileged,
confidential, and proprietary data. If you are not the intended recipient,
please advise the sender by replying promptly to this email and then delete
and destroy this email and any attachments without any further use, copying
or forwarding.