PERFORM statement inside procedure

[Rajat Katyal]

Hi:

In postgres documentation its written that if we execute query as PERFORM query inside our stored procedure; then the special variable FOUND is set totrue if the query produced at least one row, or false if it produced no rows.

But FOUND variable is always returning true even my query is returning 0 records.

Please suggest me the solution.

Thanks and Regards,
Rajat.

[Bill Moran]

Rajat Katyal wrote:

Hi:

In postgres documentation its written that if we execute query as
PERFORM /query /inside our stored procedure/;/ *then the special
variable FOUND is set to true if the query produced at least one row, or
false if it produced no rows.*
**
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_)

You might do well to post more details about your usage. Best would be to
post the actual stored procedure you're having problems with, along with the
version of Postgres in use.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

Bill Moran <wm****@potenti altech.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

[Rajat Katyal]

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"."trans form_customer_b illinginsertupd ate" () 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_cust omer_billing" set '';
IF NEW."cust_acct_ no" is not null then
updateSql := updateSql || '' "cust_acct_ no" = '' || quote_literal(N EW."cust_acct_n o") || '', '';
END IF;
IF NEW."inv_no" is not null then
updateSql := updateSql || '' "inv_no" = '' || quote_literal(N EW."inv_no") || '', '';
END IF;
IF NEW."inv_date" is not null then
updateSql := updateSql || '' "inv_date" = '' || quote_literal(N EW."inv_date") || '', '';
END IF;
IF NEW."inv_co_ori g" is not null then
updateSql := updateSql || '' "inv_co_ori g" = '' || quote_literal(N EW."inv_co_orig ") || '', '';
END IF;
IF NEW."inv_tot_am t" is not null then
updateSql := updateSql || '' "inv_tot_am t" = '' || quote_literal(N EW."inv_tot_amt ") || '', '';
END IF;
IF NEW."inv_disc_a mt" is not null then
updateSql := updateSql || '' "inv_disc_a mt" = '' || quote_literal(N EW."inv_disc_am t") || '', '';
END IF;
IF NEW."inv_net_am t" is not null then
updateSql := updateSql || '' "inv_net_am t" = '' || quote_literal(N EW."inv_net_amt ") || '', '';
END IF;
IF NEW."cust_pay_a mt" is not null then
updateSql := updateSql || '' "cust_pay_a mt" = '' || quote_literal(N EW."cust_pay_am t") || '', '';
END IF;
IF NEW."cust_pay_d ate" is not null then
updateSql := updateSql || '' "cust_pay_d ate" = '' || quote_literal(N EW."cust_pay_da te") || '', '';
END IF;
IF NEW."cust_tot_o ut_bal" is not null then
updateSql := updateSql || '' "cust_tot_out_b al" = '' || quote_literal(N EW."cust_tot_ou t_bal") || '', '';
END IF;
updateSql := substring(updat eSql, 0, length(updateSq l)-1);
checkPKSql := ''select * from "transform_cust omer_billing" '';
updateSql := updateSql || '' where "inv_no" = '' || quote_literal(n ew."inv_no");
checkPKSql := checkPKSql || '' where "inv_no" = '' || quote_literal(n ew.."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****@potenti altech.com>
Cc: "Rajat Katyal" <ra****@inteles oftech.com>; <pg***********@ postgresql.org>
Sent: Wednesday, April 07, 2004 2:14 AM
Subject: Re: [GENERAL] PERFORM statement inside procedure

Bill Moran <wm****@potenti altech.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

[Tom Lane]

"Rajat Katyal" <ra****@inteles oftech.com> writes:

checkPKSql := ''select * from "transform_cust omer_billing" '';
checkPKSql := checkPKSql || '' where "inv_no" = '' || quote_literal(n ew=
."inv_no");
PERFORM checkPKSql;

You seem to be confusing PERFORM with EXECUTE. They are very different.
The above PERFORM is really equivalent to
SELECT 'select ...';
which naturally yields a row containing a not-very-useful string value.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Rajat Katyal]

Actually my problem is PERFORM is not updating the FOUND variable to false
even when my query return no rows. Can you please tell me the better way to
use PERFORM so that by running my select query I just come to know whether
it returns 0 rows or not.

Regards,
Rajat.
----- Original Message -----
From: "Tom Lane" <tg*@sss.pgh.pa .us>
To: "Rajat Katyal" <ra****@inteles oftech.com>
Cc: <pg***********@ postgresql.org>
Sent: Wednesday, April 07, 2004 11:15 AM
Subject: Re: [GENERAL] PERFORM statement inside procedure

"Rajat Katyal" <ra****@inteles oftech.com> writes:

checkPKSql := ''select * from "transform_cust omer_billing" '';
checkPKSql := checkPKSql || '' where "inv_no" = '' || quote_literal(n ew=
."inv_no");
PERFORM checkPKSql;

You seem to be confusing PERFORM with EXECUTE. They are very different.
The above PERFORM is really equivalent to
SELECT 'select ...';
which naturally yields a row containing a not-very-useful string value.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Richard Huxton]

On Wednesday 07 April 2004 08:30, Rajat Katyal wrote:

Actually my problem is PERFORM is not updating the FOUND variable to false
even when my query return no rows. Can you please tell me the better way to
use PERFORM so that by running my select query I just come to know whether
it returns 0 rows or not.

No, what Tom's saying is that PERFORM doesn't take a string - you are passing
it a string. Since perform is equivalent to SELECT that means you are doing
SELECT 'SELECT true'
which returns one row, containing one column: 'SELECT true'

If you want to execute a string as a query, you use EXECUTE. Execute slows the
process down because it actually runs the query.

If you want to use PERFORM you'll need to write something like:

PERFORM SELECT true FROM transform_custo mer_billing WHERE inv_no = NEW.inv_no;
IF FOUND THEN
....

(The only reason I select true rather than * is so I don't think I'm going to
use the results from this query when I look at the code 12 months from now).

PS - that will slow the process down again. I'm not sure you want to be doing
what you're trying to do. Your original problem (a couple of weeks ago?)
seemed to be inserts/updates were half the speed if you checked for the
existence of the row yourself. Not surprising, you're doing two things. But,
you wanted to do this because you didn't know if you were updating or
inserting.

In cases like this, I prefer to remove the unknown. Where I need a customer to
have a balance total, I add a trigger to the customer table so that every
time a new customer is inserted, so is a zeroed row to the balance table.
Deny deletion of balance rows where its customer still exists and you can
safely issue updates all the time.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster