From: | "Zíka Aleš, Ing(dot)" <Ales(dot)Zika(at)pel(dot)br(dot)ds(dot)mfcr(dot)cz> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Bug in SQL function with returntype void |
Date: | 2003-06-12 09:45:00 |
Message-ID: | 952EEDA52038D711AEA10002A562DC410CF7BB@www |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Your name : Ales Zika
Your email address : Ales(dot)Zika(at)seznam(dot)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 | 100
two | 1000
three | -1000
four | -1500
five | -100
(5 rows)
test=# SELECT clean_emp();
clean_emp
-----------
NULL
(1 row)
test=# SELECT * FROM emp;
name | salary
------+--------
one | 100
two | 1000
four | -1500
five | -100
(4 rows)
test=# SELECT clean_emp();
clean_emp
-----------
NULL
(1 row)
test=# SELECT * FROM emp;
name | salary
------+--------
one | 100
two | 1000
five | -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
From | Date | Subject | |
---|---|---|---|
Next Message | Serge Obeuf | 2003-06-12 12:13:33 | pg_dumpall not working in batch |
Previous Message | Arthur Ward | 2003-06-12 01:52:39 | Re: 7.3.3 - plpython & trigger problem |