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
