Function call

From: "Nico Callewaert" <callewaert(dot)nico(at)telenet(dot)be>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Function call
Date: 2009-01-24 00:43:01
Message-ID: 08A9462704644A0F836FD72C8ED4CD6C@etsinformatics.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi,

I have written my first function, but I'm unable to execute it.

Here is the function body and error message.

CREATE OR REPLACE FUNCTION update_afdeling(update_type text, in_afd_id smallint, in_afd_omschrijving text, in_date_changed timestamp without time zone)
RETURNS integer AS
$BODY$
DECLARE
v_return smallint;
v_date_changed timestamp;
BEGIN
v_date_changed = NULL;

IF (update_type = 'I') THEN
SELECT nextval('gen_afdeling_id') INTO v_return;

INSERT INTO tbl_afdeling(
afd_id,
afd_omschrijving,
last_user,
date_changed)
VALUES(
v_return,
in_afd_omschrijving,
current_user,
current_timestamp);
ELSE
IF (update_type = 'U') THEN
v_return = in_afd_id;

SELECT date_changed INTO v_date_changed
FROM tbl_afdeling
WHERE afd_id = v_return;

IF (NOT FOUND) THEN
RETURN -2;
ELSE
IF (ABS(v_date_changed - in_date_changed) < 0.00002) THEN
RETURN -1;
ELSE
UPDATE tbl_afdeling
SET afd_omschrijving = in_afd_omschrijving,
last_user = current_user,
date_changed = current_timestamp
WHERE afd_id = v_return;
END IF;
END IF;
ELSE
IF (update_type = 'D') THEN
DELETE FROM tbl_afdeling
WHERE afd_id = in_afd_id;
END IF;
END IF;

UPDATE tbl_table_last_change
SET last_user = current_user,
date_changed = current_timestamp
WHERE tlc_table = 'TBL_AFDELING';
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION update_afdeling(text, smallint, text, timestamp without time zone) OWNER TO postgres;

Function call :

select update_afdeling('I', 1, 'afdeling 1', current_timestamp)

Error message :

ERROR: function update_afdeling(unknown, integer, unknown, timestamp with time zone) does not exist
LINE 1: select update_afdeling('I', 1, 'afdeling 1', current_timesta...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

********** Error **********

ERROR: function update_afdeling(unknown, integer, unknown, timestamp with time zone) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 8

What I understand is : the function doesn't match the parameters in the call, but I don't have any idea what I'm doing wrong...
I already tried to replace the parameter types with VARCHAR instead of TEXT, but same error.
Many thanks in advance.
Nico

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Andreas Kretschmer 2009-01-24 10:23:29 Re: Function call
Previous Message Daniel Staal 2009-01-23 20:39:42 SQL Question: Averages of intervals.