Hi:
Iam using Postgres version 7.3. As requested I have pasted the stored procedure below. Here Iam using PERFORM statement (marked in bold) but FOUND variable next to this statement always returns true. I dont want to use EXECUTE as it slow down the process.
Please suggest me the solution at your earliest.
Regards,
Rajat.
CREATE FUNCTION "public"."transform_customer_billinginsertupda te" () RETURNS trigger AS'
declare
updateSql varchar;
checkPKSql varchar;
recordValue varchar;
tempField varchar;
relName varchar;
attrName varchar;
debugMode varchar;
begin
attrName := TG_ARGV[1];
relName := TG_ARGV[0];
updateSql = ''UPDATE "transform_customer_billing" set '';
IF NEW."cust_acct_no" is not null then
updateSql := updateSql || '' "cust_acct_no" = '' || quote_literal(NEW."cust_acct_no") || '', '';
END IF;
IF NEW."inv_no" is not null then
updateSql := updateSql || '' "inv_no" = '' || quote_literal(NEW."inv_no") || '', '';
END IF;
IF NEW."inv_date" is not null then
updateSql := updateSql || '' "inv_date" = '' || quote_literal(NEW."inv_date") || '', '';
END IF;
IF NEW."inv_co_orig" is not null then
updateSql := updateSql || '' "inv_co_orig" = '' || quote_literal(NEW."inv_co_orig") || '', '';
END IF;
IF NEW."inv_tot_amt" is not null then
updateSql := updateSql || '' "inv_tot_amt" = '' || quote_literal(NEW."inv_tot_amt") || '', '';
END IF;
IF NEW."inv_disc_amt" is not null then
updateSql := updateSql || '' "inv_disc_amt" = '' || quote_literal(NEW."inv_disc_amt") || '', '';
END IF;
IF NEW."inv_net_amt" is not null then
updateSql := updateSql || '' "inv_net_amt" = '' || quote_literal(NEW."inv_net_amt") || '', '';
END IF;
IF NEW."cust_pay_amt" is not null then
updateSql := updateSql || '' "cust_pay_amt" = '' || quote_literal(NEW."cust_pay_amt") || '', '';
END IF;
IF NEW."cust_pay_date" is not null then
updateSql := updateSql || '' "cust_pay_date" = '' || quote_literal(NEW."cust_pay_date") || '', '';
END IF;
IF NEW."cust_tot_out_bal" is not null then
updateSql := updateSql || '' "cust_tot_out_bal" = '' || quote_literal(NEW."cust_tot_out_bal") || '', '';
END IF;
updateSql := substring(updateSql, 0, length(updateSql)-1);
checkPKSql := ''select * from "transform_customer_billing" '';
updateSql := updateSql || '' where "inv_no" = '' || quote_literal(new."inv_no");
checkPKSql := checkPKSql || '' where "inv_no" = '' || quote_literal(new.."inv_no");
PERFORM checkPKSql;
if FOUND then
execute updateSql;
return null;
else
return new;
end if;
end;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
----- Original Message -----
From: "Tom Lane" <tg*@sss.pgh.pa.us>
To: "Bill Moran" <wm****@potentialtech.com>
Cc: "Rajat Katyal" <ra****@intelesoftech.com>; <pg***********@postgresql.org>
Sent: Wednesday, April 07, 2004 2:14 AM
Subject: Re: [GENERAL] PERFORM statement inside procedure
Bill Moran <wm****@potentialtech.com> writes: Rajat Katyal wrote: But FOUND variable is always returning true even my query is
returning *0 records.*
FOUND appears to work correctly in the hundreds of stored procedures I wrote
last month. (At least, I haven't found any problems _yet_)
Works for me too, in recent releases. I believe PERFORM did not originally
set FOUND ... are you reading the documentation that goes with your server
version?
[ digs in CVS logs... ] Here we go:
2002-06-24 19:12 tgl
* src/pl/plpgsql/src/pl_exec.c: plpgsql's PERFORM statement now
sets FOUND depending on whether any rows were returned by the
performed query. Per recent pgsql-general discussion.
So it should work in 7.3 or later.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match