Re: [GENERAL] PERFORM statement inside procedure
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_customer_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
Re: [GENERAL] PERFORM statement inside procedure
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" <[EMAIL PROTECTED]> To: "Rajat Katyal" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, April 07, 2004 11:15 AM Subject: Re: [GENERAL] PERFORM statement inside procedure > "Rajat Katyal" <[EMAIL PROTECTED]> writes: > > checkPKSql := ''select * from "transform_customer_billing" ''; > > checkPKSql := checkPKSql || '' where "inv_no" = '' || quote_literal(new= > > ."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
Re: [GENERAL] PERFORM statement inside procedure
"Rajat Katyal" <[EMAIL PROTECTED]> writes: > checkPKSql := ''select * from "transform_customer_billing" ''; > checkPKSql := checkPKSql || '' where "inv_no" = '' || quote_literal(new= > ."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
Re: [GENERAL] PERFORM statement inside procedure
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
[GENERAL] 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 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. Please suggest me the solution. Thanks and Regards, Rajat.
