473,836 Members | 1,487 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

PERFORM statement inside procedure

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.

Nov 23 '05 #1
6 17929
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

Nov 23 '05 #2
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

Nov 23 '05 #3
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


Nov 23 '05 #4
"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

Nov 23 '05 #5
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

Nov 23 '05 #6
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

Nov 23 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1665
by: - | last post by:
is there an equivalent of postgresql's PERFORM statement in mysql?
3
19818
by: sck10 | last post by:
Hello, I am trying to use an If Then statement inside of a repeater control. However, I am getting the following error: Expression expected. Any help would be appreciated. Thanks in advance,
1
7302
by: syntego | last post by:
I am using DB2 V8 fixpack 10 and have the following issue: >From a User Defined Function, I can assign multiple variables in a single statement as follows: -- Get the first update transaction for the mailing event. set (fUpdateTS, fAddrType, fAccountID, fOpCode) = (select AddTS, UpdateTypeCode, AccountID, OpCode FROM
4
4503
exoskeleton
by: exoskeleton | last post by:
hi..please help me! i have an if statement inside the SQL like this: $SQL = "INSERT into table1 (name,address,phone,email) values ('$name','$address',". if (strlen($phone) > = 1) { "'$phone',". } else {
1
1848
by: itsraghz | last post by:
Hello All, I am using a Dynamic SQL Statement inside a Stored Procedure. I am using IBM DB2 V8.1 database. When i want to have the values to be filled in dynamically, i use the parameter marker (?). I dont have any issues if the value is of type VARCHAR. But I get an error if i bind a value other than Varchar (String) say, an Integer. The error I get is "The statement "<stmt what i prepare>" is too long". See the statements i use : ...
5
8189
by: clear1140 | last post by:
Good day!! I was wondering if it is possible to put an if else statement inside a where clause of an sql statement... you see i would like my update statement to do this: update trial_clear set num = @count2 /* @count2 is a integer passed*/ where if (select top 1 def from trial_clear where num is NULL) is NULL def is NULL else
3
1735
by: Nsim | last post by:
How to use RecordSet inside procedure?
1
2321
by: jessy | last post by:
i was wondering if i can execute some sql statement inside JS code ?? script type="text/javascript"> function confirm_delete() { var answer=confirm("Are You Sure You Want to Delete ?"); if(answer==true) { // sql statement which deletes that row from DB alert(" Title Deleted"); }
2
2261
Prakash Gnana
by: Prakash Gnana | last post by:
Can i use break(); and continue(); statement inside if loop?
0
9814
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10838
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10544
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10585
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7788
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6977
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5645
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5821
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4447
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.