From: | Sharon Cowling <sharon(dot)cowling(at)sslnz(dot)com> |
---|---|
To: | "Pgsql-Novice (E-mail)" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Function Problem |
Date: | 2002-01-30 01:47:00 |
Message-ID: | 200201300142.g0U1gFt16798@lambton.sslnz.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Oliver Elphick | 2002-01-30 06:58:47 | Re: [Fwd: Re: pgaccess connection problems] |
Previous Message | Frank Bax | 2002-01-30 01:21:04 | Re: text field |