Re: PERFORM statement inside procedure

["Rajat Katyal" <rajatk@intelesoftech.com>]

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@sss.pgh.pa.us>

To: "Bill Moran" <wmoran@potentialtech.com>

Cc: "Rajat Katyal" <rajatk@intelesoftech.com>; <pgsql-general@postgresql.org>

Sent: Wednesday, April 07, 2004 2:14 AM

Subject: Re: PERFORM statement inside procedure

> Bill Moran <wmoran@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
>