Re: PostgreSQL 9.2, SQL functions' named vs numbered parameters.

2012/6/13 Chris Travers <chris(dot)travers(at)gmail(dot)com>:
> 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.

you can mix named parameters and holders without problems:

postgres=# create or replace function fx(a int)
postgres-# returns int as $$ select $1; $$ language sql;
CREATE FUNCTION
postgres=# select fx(10);
fx
----
10
(1 row)

postgres=# select version();
version
----------------------------------------------------------------------------------------------------
PostgreSQL 9.2beta1 on x86_64-unknown-linux-gnu, compiled by gcc
(GCC) 4.7.0 20120507 (Red Hat 4.7.
(1 row)

Regards

Pavel

>
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general