Thread: Bug in SQL function with returntype void
Your name : Ales Zika
Your email address : Ales.Zika@seznam.cz
System Configuration
--------------------- Architecture (example: Intel Pentium) : Intel Pentium
Operating System (example: Linux 2.0.26 ELF) : Windows 2002 Professionl
CZ + cygwin
PostgreSQL version (example: PostgreSQL-7.3.3): PostgreSQL-7.3.3
Compiler used (example: gcc 2.95.2) : originally compiled EXE
from cygwin
Please enter a FULL description of your problem:
------------------------------------------------I tried the example "CREATE FUNCTION clean_EMP () RETURNS void AS
'DELETE FROM EMP WHERE EMP.salary <= 0' LANGUAGE SQL;" in chapter 9.2.1 of
Programmer's guide.When the function was invoked, it delete only one tuple, although
there were more tuples satisfying the WHERE condition. During secont
invocation it deleted next tuple and so on.
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
CREATE TABLE emp (name text, salary int);
INSERT INTO emp VALUES ('one', 100);
INSERT INTO emp VALUES ('two', 1000);
INSERT INTO emp VALUES ('three', -1000);
INSERT INTO emp VALUES ('four', -1500);
INSERT INTO emp VALUES ('five', -100);
CREATE FUNCTION clean_emp () RETURNS int AS 'DELETE FROM emp WHERE salary <=
0; SELECT 1;' LANGUAGE SQL;
test=# SELECT * FROM emp;name | salary
-------+--------one | 100two | 1000three | -1000four | -1500five | -100
(5 rows)
test=# SELECT clean_emp();clean_emp
-----------NULL
(1 row)
test=# SELECT * FROM emp;name | salary
------+--------one | 100two | 1000four | -1500five | -100
(4 rows)
test=# SELECT clean_emp();clean_emp
-----------NULL
(1 row)
test=# SELECT * FROM emp;name | salary
------+--------one | 100two | 1000five | -100
(3 rows)
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
Change ther returntype of the function to int a add a dummy "SELECT
1;" into its body:
CREATE FUNCTION clean_emp () RETURNS int AS 'DELETE FROM emp WHERE salary <=
0; SELECT 1; ' LANGUAGE SQL;
Yours sincerely,
Ales Zika
"Zíka Aleš, Ing." <Ales.Zika@pel.br.ds.mfcr.cz> writes:
> I tried the example "CREATE FUNCTION clean_EMP () RETURNS void AS
> 'DELETE FROM EMP WHERE EMP.salary <= 0' LANGUAGE SQL;" in chapter 9.2.1 of
> Programmer's guide.
> When the function was invoked, it delete only one tuple, although
> there were more tuples satisfying the WHERE condition. During secont
> invocation it deleted next tuple and so on.
Good catch. I've applied the attached patch.
regards, tom lane
*** src/backend/executor/functions.c.orig Wed Sep 4 16:31:18 2002
--- src/backend/executor/functions.c Thu Jun 12 13:26:12 2003
***************
*** 273,289 **** if (es->qd->operation == CMD_UTILITY) {
- /*
- * Process a utility command. (create, destroy...) DZ - 30-8-1996
- */ ProcessUtility(es->qd->parsetree->utilityStmt, es->qd->dest, NULL);
- if (!LAST_POSTQUEL_COMMAND(es))
- CommandCounterIncrement(); return (TupleTableSlot *) NULL; }
! /* If it's not the last command, just run it to completion */
! count = (LAST_POSTQUEL_COMMAND(es)) ? 1L : 0L; return ExecutorRun(es->qd, es->estate, ForwardScanDirection,
count);}
--- 273,291 ---- if (es->qd->operation == CMD_UTILITY) {
ProcessUtility(es->qd->parsetree->utilityStmt,es->qd->dest, NULL); return (TupleTableSlot *) NULL; }
! /*
! * If it's the function's last command, and it's a SELECT, fetch one
! * row at a time so we can return the results. Otherwise just run it
! * to completion.
! */
! if (LAST_POSTQUEL_COMMAND(es) && es->qd->operation == CMD_SELECT)
! count = 1L;
! else
! count = 0L; return ExecutorRun(es->qd, es->estate, ForwardScanDirection, count); }
Thanks. That bug looked really weird!
---------------------------------------------------------------------------
Tom Lane wrote:
> "Zíka Aleš, Ing." <Ales.Zika@pel.br.ds.mfcr.cz> writes:
> > I tried the example "CREATE FUNCTION clean_EMP () RETURNS void AS
> > 'DELETE FROM EMP WHERE EMP.salary <= 0' LANGUAGE SQL;" in chapter 9.2.1 of
> > Programmer's guide.
> > When the function was invoked, it delete only one tuple, although
> > there were more tuples satisfying the WHERE condition. During secont
> > invocation it deleted next tuple and so on.
>
> Good catch. I've applied the attached patch.
>
> regards, tom lane
>
>
> *** src/backend/executor/functions.c.orig Wed Sep 4 16:31:18 2002
> --- src/backend/executor/functions.c Thu Jun 12 13:26:12 2003
> ***************
> *** 273,289 ****
>
> if (es->qd->operation == CMD_UTILITY)
> {
> - /*
> - * Process a utility command. (create, destroy...) DZ - 30-8-1996
> - */
> ProcessUtility(es->qd->parsetree->utilityStmt, es->qd->dest, NULL);
> - if (!LAST_POSTQUEL_COMMAND(es))
> - CommandCounterIncrement();
> return (TupleTableSlot *) NULL;
> }
>
> ! /* If it's not the last command, just run it to completion */
> ! count = (LAST_POSTQUEL_COMMAND(es)) ? 1L : 0L;
>
> return ExecutorRun(es->qd, es->estate, ForwardScanDirection, count);
> }
> --- 273,291 ----
>
> if (es->qd->operation == CMD_UTILITY)
> {
> ProcessUtility(es->qd->parsetree->utilityStmt, es->qd->dest, NULL);
> return (TupleTableSlot *) NULL;
> }
>
> ! /*
> ! * If it's the function's last command, and it's a SELECT, fetch one
> ! * row at a time so we can return the results. Otherwise just run it
> ! * to completion.
> ! */
> ! if (LAST_POSTQUEL_COMMAND(es) && es->qd->operation == CMD_SELECT)
> ! count = 1L;
> ! else
> ! count = 0L;
>
> return ExecutorRun(es->qd, es->estate, ForwardScanDirection, count);
> }
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073