Function call - Mailing list pgsql-novice
| From | Nico Callewaert |
|---|---|
| Subject | Function call |
| Date | |
| Msg-id | 08A9462704644A0F836FD72C8ED4CD6C@etsinformatics.local Whole thread Raw |
| Responses |
Re: Function call
(Andreas Kretschmer <akretschmer@spamfence.net>)
|
| List | 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;
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;
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;
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;
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;
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;
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.
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
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
pgsql-novice by date: