Re: Passing NULL values in dblink function call

Thanks, that worked.
I have another problem in this code, can you help me solve it,
Please see the two highlighted line of codes, In the remote function im returning the table containing three columns. This works fine as I tried calling the remote function in a single explicit query.
But when I call the remote function from the local function in the end results I get a single column result of type record which is a comma separated record containing the three columns.
This seems to be some problem with local function.
Can you please help with this too.?

Regards,
Piyush

From: Attila Kiss [mailto:kiss(dot)attila(at)dardanis(dot)hu]
Sent: Thursday, October 27, 2016 2:24 PM
To: Jaisingkar, Piyush
Subject: RE: [GENERAL] Passing NULL values in dblink function call

Hello,
I am not sure about this, but have you tried
%L instead of %s? It seems the ''%s'' in the format method returns '''' to NULL::varchar, but it returns ''NULL'' if you use %L with NULL::varchar. And, if you use %L you do not have to put '-s in your query, because format escapes it properly(if I am right).
I hope this helps.
Sincerely,

Attila Kiss

-----eredeti Üzenet-----
Feladó: Jaisingkar, Piyush <Piyush(dot)Jaisingkar(at)nttdata(dot)com<mailto:Piyush(dot)Jaisingkar(at)nttdata(dot)com>>
Elküldött: Csütörtök 2016.10.27 09:06
Tárgy: [GENERAL] Passing NULL values in dblink function call
Címzett: pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)org>;

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.

______________________________________________________________________
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.