Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group