Thread: Function call
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
Nico Callewaert <callewaert.nico@telenet.be> schrieb:
> 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.
Cast the current_timestamp to timestamp:
select update_afdeling('I', 1, 'afdeling 1', current_timestamp::timestamp)
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Hi again,
I tried your solution, but it gives me still the same error message. But
there is a dotted line under the funcion name, like the error is pointing to
the function name. Is that the correct way to execute a function, using
SELECT ?
Thanks again, Nico
----- Original Message -----
From: "Andreas Kretschmer" <akretschmer@spamfence.net>
To: <pgsql-novice@postgresql.org>
Sent: Saturday, January 24, 2009 11:23 AM
Subject: Re: [NOVICE] Function call
> Nico Callewaert <callewaert.nico@telenet.be> schrieb:
>> 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.
>
> Cast the current_timestamp to timestamp:
>
> select update_afdeling('I', 1, 'afdeling 1', current_timestamp::timestamp)
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknown)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>
>
Nico Callewaert <callewaert.nico@telenet.be> schrieb:
> Hi again,
>
> I tried your solution, but it gives me still the same error message. But
> there is a dotted line under the funcion name, like the error is pointing to
> the function name. Is that the correct way to execute a function, using
> SELECT ?
Sorry, you should also cast the 1 to smallint:
test=# CREATE OR REPLACE FUNCTION upd_afdeling(update_type text, in_afd_id smallint, in_afd_omschrijving text,
in_date_changedtimestamp without time zone) RETURNS integer AS $BODY$ BEGIN return 1; END; $BODY$ LANGUAGE plpgsql
VOLATILECOST 100;
CREATE FUNCTION
Zeit: 0,607 ms
test=*# select upd_afdeling('I'::text, 1::smallint, 'afdeling 1'::text, current_timestamp::timestamp);
upd_afdeling
--------------
1
(1 Zeile)
Zeit: 0,281 ms
test=*# select upd_afdeling('I'::text, 1, 'afdeling 1'::text, current_timestamp::timestamp);
ERROR: function upd_afdeling(text, integer, text, timestamp without time zone) does not exist
LINE 1: select upd_afdeling('I'::text, 1, 'afdeling 1'::text, curren...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
>
> Thanks again, Nico
>
>
> ----- Original Message ----- From: "Andreas Kretschmer"
Please, no top-posting:
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing in e-mail?
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
> Nico Callewaert <callewaert.nico@telenet.be> schrieb:
>
>> Hi again,
>>
>> I tried your solution, but it gives me still the same error message. But
>> there is a dotted line under the funcion name, like the error is pointing
>> to
>> the function name. Is that the correct way to execute a function, using
>> SELECT ?
>
> Sorry, you should also cast the 1 to smallint:
>
> test=# CREATE OR REPLACE FUNCTION upd_afdeling(update_type text, in_afd_id
> smallint, in_afd_omschrijving text, in_date_changed timestamp without time
> zone) RETURNS integer AS $BODY$ BEGIN return 1; END; $BODY$ LANGUAGE
> plpgsql VOLATILE COST 100;
> CREATE FUNCTION
> Zeit: 0,607 ms
> test=*# select upd_afdeling('I'::text, 1::smallint, 'afdeling 1'::text,
> current_timestamp::timestamp);
> upd_afdeling
> --------------
> 1
Hello again,
This is working ! Thanks...
Is it better to use varchar as parameter, or just "text" ? The colmuns I'm
updating through this function are varchars.
I'm still surprised about 1 thing : it seems PostgreSQL is not checking for
existing tables or columns.
Like this : UPDATE tbl_table_last_change
SET last_user = current_user,
Table tbl_table_last_change doesn't exist yet, but still postgreSQL is
acception the function definition...
So, I guess, there is no early checking on tables/fields ?
Thanks for your help, for sure I would never find the solution, I had no
idea about the casting...
Best regards, Nico
Nico Callewaert <callewaert.nico@telenet.be> schrieb: > Hello again, > > This is working ! Thanks... > Is it better to use varchar as parameter, or just "text" ? The colmuns > I'm updating through this function are varchars. It's puny. > I'm still surprised about 1 thing : it seems PostgreSQL is not checking > for existing tables or columns. > > Like this : UPDATE tbl_table_last_change > SET last_user = current_user, > > Table tbl_table_last_change doesn't exist yet, but still postgreSQL is > acception the function definition... Right, the code is syntactically correct. > So, I guess, there is no early checking on tables/fields ? Right. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°