Home > mailing lists

Re: PERFORM statement inside procedure - Mailing list pgsql-general

From	Rajat Katyal
Subject	Re: PERFORM statement inside procedure
Date	April 7, 2004 04:37:36
Msg-id	003701c41c5a$2bf7f000$2105a8c0@bgdev001 Whole thread Raw
In response to	PERFORM statement inside procedure ("Rajat Katyal" <rajatk@intelesoftech.com>)
Responses	Re: PERFORM statement inside procedure
List	pgsql-general

Tree view

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: [GENERAL] 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
>

pgsql-general by date:

From: Gregory Wood
Date: 07 April 2004, 04:29:08
Subject: Re: Can we have time based triggers in Postgresql??

From: Joe Conway
Date: 07 April 2004, 04:45:17
Subject: Re: Cursors and Transactions, why?

Re: PERFORM statement inside procedure - Mailing list pgsql-general

Previous

Next