Hi;
In another thread it has been mentioned that SQL language functions in
9.2 will accept named parameters and that you can't mix named and
numbered parameters. Can anyone confirm this? I am a bit concerned
this will break a lot of LSMB stored procedures and that we won't be
able to support 8.4-9.1 and 9.2+ with the same versions of the
software.
We use named parameters not only for function readability but also to
provide information to the application as to what data the stored
procedure expects. You can see more at
http://ledgersmbdev.blogspot.com/2011/10/introduction-to-soda.html
Because our application tends to depend often on being able to look up
the names of parameters, any time we pass parameters to an SQL
language function these are named, but identified by number in the
function body. Consequently we have functions like:
CREATE OR REPLACE FUNCTION asset_dep_get_usable_life_yr
(in_usable_life numeric, in_start_date date, in_dep_date date)
returns numeric as
$$
SELECT CASE WHEN $3 IS NULL or get_fractional_year($2, $3) > $1
then $1
WHEN get_fractional_year($2, $3) < 0
THEN 0
ELSE get_fractional_year($2, $3)
END;
$$ language sql;
or
CREATE OR REPLACE FUNCTION asset_item__add_note(in_id int, in_subject
text, in_note text)
RETURNS asset_note AS
$$
INSERT INTO asset_note (ref_key, subject, note) values ($1, $2, $3);
SELECT * FROM asset_note WHERE id = currval('note_id_seq');
$$ language sql;
We do this a lot. I think we have about 122 SQL language functions,
and the only way to get this to work will be to work across versions
if this restriction is there will be to port every one to PL/PGSQL,
and I am not sure where/when we may have performance problems from
that.
Ideally there would be some way to have backwards-compatiblity here,
but if that's not likely or an option, it would be helpful for me to
get some clarification for that now so that I can document the problem
and warn users. We'd probably also require 9.2 sooner rather than
later.
Best Wishes,
Chris Travers
