Обсуждение: 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
>