"Input parameter mismatch" error after altering external function into PSQL
function
------------------------------------------------------------------------------------
Key: CORE-5776
URL: http://tracker.firebirdsql.org/browse/CORE-5776
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.3, 4.0 Alpha 1, 3.0.2, 3.0.1, 3.0.0, 4.0 Initial
Reporter: Vlad Khorsun
Firebird 3 introduced PSQL FUNCTION's and now it is recommended to convert
UDF's into PSQL functions when possible.
ALTER FUNCTION statement could convert UDF into PSQL functions but sometime
PSQL function become unusable after
such conversion. Example:
a) declare UDF
declare external function sright
varchar(100) by descriptor, smallint,
varchar(100) by descriptor returns parameter 3
entry_point 'right' module_name 'fbudf';
b) make sure it works
select sright('function', 2) from rdb$database;
SRIGHT
===============================================================================
on
c) convert UDF into PSQL function
commit;
set term ^;
alter function sright (str varchar(100), len int)
returns varchar(100)
as
begin
return right(str, len);
end^
set term ;^
d) check if it works
select sright('function', 2) from rdb$database;
Statement failed, SQLSTATE = 07001
Dynamic SQL Error
-Input parameter mismatch for function SRIGHT
The problem is that ALTER FUNCTION didn't changed
RDB$FUNCTIONS.RDB$RETURN_ARGUMENT value :
a) original UDF
select rdb$return_argument from rdb$functions
where rdb$function_name = 'SRIGHT';
RDB$RETURN_ARGUMENT
===================
3
select rdb$argument_position, rdb$argument_name from rdb$function_arguments
where rdb$function_name = 'SRIGHT';
RDB$ARGUMENT_POSITION RDB$ARGUMENT_NAME
===================== ===============================
1 <null>
2 <null>
3 <null>
b) after ALTER FUNCTION
select rdb$return_argument from rdb$functions
where rdb$function_name = 'SRIGHT';
RDB$RETURN_ARGUMENT
===================
3
select rdb$argument_position, rdb$argument_name from rdb$function_arguments
where rdb$function_name = 'SRIGHT';
RDB$ARGUMENT_POSITION RDB$ARGUMENT_NAME
===================== ===============================
0 <null>
1 STR
2 LEN
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
