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.jaising...@nttdata.com
NTT DATA Global Delivery Services Private Limited Consulting | Digital |
Managed Services | Industry Solutions
Learn more:
[Description: Description: Description:
cid:image005.jpg@01D193F0.F70B44C0]<http://americas.nttdata.com/>
[Description: Description: Description:
cid:image009.jpg@01D193F0.F70B44C0]<http://www.linkedin.com/company/ntt-data-americas>
[Description: Description: Description:
cid:image010.jpg@01D193F0.F70B44C0]<https://twitter.com/NTTDATAAmericas>
[Description: Description: Description:
cid:image011.jpg@01D193F0.F70B44C0]<https://www.facebook.com/NTTDATAAmericas>
______________________________________________________________________
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.
