Thread: Function Problem
I have a problem with a function I am creating: CREATE FUNCTION overdue_key() returns int as ' BEGIN UPDATE faps_key SET status = ''Overdue'' WHERE (select key_code from faps_key where date_key_due > current_date); RETURN 1; END;' LANGUAGE 'plpgsql'; user=> select overdue_key(); ERROR: WHERE clause must return type bool, not type varchar I understand the error message, but I am unsure as to how to go about achieving my desired results. I know how to do this using cursors inside a stored procedure in Oracle, but I understand that I cannot use cursors insidea function in Postgres in the version I am using: 7.1.3 If a key is overdue - (the date_key_due > current_date) then I want to update the status of each over due key to 'Overdue' Example of faps_table: user=> select key_code, permit_id, date_key_issued, date_key_due, status from faps_key; key_code | permit_id | date_key_issued | date_key_due | status ----------+-----------+-----------------+--------------+----------- B4 | | | | Available A13 | | | | Available B1 | | | | Available A11 | 1141 | 29/01/2002 | 02/02/2002 | Issued A14 | 1145 | 29/01/2002 | 12/02/2002 | Issued A12 | 1146 | 29/01/2002 | 02/02/2002 | Issued B2 | 1147 | 29/01/2002 | 03/02/2002 | Issued B3 | | | | Available B5 | 1148 | 29/01/2002 | 01/02/2002 | Issued A15 | 1149 | 29/01/2002 | 30/01/2002 | Issued List of current overdue keys, they have a status of issued, when function is run I want to update the status to 'Overdue' user=> select key_code, permit_id, status from faps_key where date_key_due > current_date; key_code | permit_id | status ----------+-----------+-------- A11 | 1141 | Issued A14 | 1145 | Issued A12 | 1146 | Issued B2 | 1147 | Issued B5 | 1148 | Issued (5 rows) Any ideas, pointers, documentation?! Regards, Sharon Cowling
02-01-30 02.47 Sharon Cowling sharon.cowling@sslnz.com
> I know how to do this using cursors inside a stored procedure in Oracle, but I
> understand that I cannot use cursors inside a function in Postgres in the
> version I am using: 7.1.3
You can use cursors i 7.1.3. I suggest something like:
CREATE FUNCTION overdue_key() returns int as '
DECLARE
row record;
BEGIN
FOR row IN
SELECT t key_code FROM faps_key
WHERE date_key_due > now()
LOOP
UPDATE faps_key
SET status = ''Overdue''
WHERE key_code = row.keycode;
END LOOP;
END;'LANGUAGE 'plpgsql';
Code is NOT tested...
Regards
Torbjörn Andersson
---------------------------------------------------
Embryo Communication phone: +46 (0) 31-774 39 11(00)
Kungsgatan 7a fax: +46 (0)31 774 07 80
S-411 19 Göteborg mobile: 0708-30 70 04
Sweden home: http://www.embryo.se/
mail: torbjorn.andersson@embryo.se
---------------------------------------------------
"Att idag tänka annorlunda än igår skiljer den vise från den envise." John
Steinbeck
Sharon, try it with WHERE date_key_due > current_date instead of your sub-select. hih ----- Original Message ----- From: "Sharon Cowling" <sharon.cowling@sslnz.com> To: "Pgsql-Novice (E-mail)" <pgsql-novice@postgresql.org> Sent: Wednesday, January 30, 2002 1:47 AM Subject: [NOVICE] Function Problem > I have a problem with a function I am creating: > > CREATE FUNCTION overdue_key() returns int as ' > BEGIN > UPDATE faps_key SET > status = ''Overdue'' > WHERE (select key_code from faps_key > where date_key_due > current_date); > RETURN 1; > END;' > LANGUAGE 'plpgsql'; > > user=> select overdue_key(); > ERROR: WHERE clause must return type bool, not type varchar > > I understand the error message, but I am unsure as to how to go about achieving my desired results. > I know how to do this using cursors inside a stored procedure in Oracle, but I understand that I cannot use cursors inside a function in Postgres in the version I am using: 7.1.3 > > If a key is overdue - (the date_key_due > current_date) then I want to update the status of each over due key to 'Overdue' > > Example of faps_table: > > user=> select key_code, permit_id, date_key_issued, date_key_due, status from faps_key; > key_code | permit_id | date_key_issued | date_key_due | status > ----------+-----------+-----------------+--------------+----------- > B4 | | | | Available > A13 | | | | Available > B1 | | | | Available > A11 | 1141 | 29/01/2002 | 02/02/2002 | Issued > A14 | 1145 | 29/01/2002 | 12/02/2002 | Issued > A12 | 1146 | 29/01/2002 | 02/02/2002 | Issued > B2 | 1147 | 29/01/2002 | 03/02/2002 | Issued > B3 | | | | Available > B5 | 1148 | 29/01/2002 | 01/02/2002 | Issued > A15 | 1149 | 29/01/2002 | 30/01/2002 | Issued > > List of current overdue keys, they have a status of issued, when function is run I want to update the status to 'Overdue' > > user=> select key_code, permit_id, status from faps_key where date_key_due > current_date; > key_code | permit_id | status > ----------+-----------+-------- > A11 | 1141 | Issued > A14 | 1145 | Issued > A12 | 1146 | Issued > B2 | 1147 | Issued > B5 | 1148 | Issued > (5 rows) > > Any ideas, pointers, documentation?! > > Regards, > > Sharon Cowling > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >