Re: PERFORM statement inside procedure

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_billinginsertupdate" () 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" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Bill Moran" <wmoran(at)potentialtech(dot)com>
Cc: "Rajat Katyal" <rajatk(at)intelesoftech(dot)com>; <pgsql-general(at)postgresql(dot)org>
Sent: Wednesday, April 07, 2004 2:14 AM
Subject: Re: [GENERAL] PERFORM statement inside procedure

> Bill Moran <wmoran(at)potentialtech(dot)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
>