pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedplpgsql: defuault parameters and constant function parameters
4+ messages / 4 participants
[nested] [flat]
* plpgsql: defuault parameters and constant function parameters
@ 2007-08-28 12:31 Roger Moloney <ramoloney@hotmail.com>
2007-08-30 00:19 ` Re: plpgsql: defuault parameters and constant function parameters Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Roger Moloney @ 2007-08-28 12:31 UTC (permalink / raw)
To: pgsql-interfaces
Hi,
Having just done the first draft of a large migration from informix to
postgres, I wanted to point out that the migration was hugely complicated by
postgres inability to define default parameters and to modify function
parameters which were not output.
Consider a simple function like (in informix):
// informix procedure
create procedure p_AddItem (pItemName char(50),
pItemDescription char(100),
pX integer
DEFAULT 1,
pY char
DEFAULT 'x',
pZ smallint)
returning INTEGER;
define vItemRef integer;
begin
-- Do input error checks
/* checks on pX, pY, pZ */
-- Add the item and return its reference
insert item (item_name, item_description) values (pItemName,
pItemDescription);
let vItemRef = DBINFO('sqca.sqerrd1');
return vItemRef;
end
and it's postgres equivalent:
// postgres function
create or replace function p_AddItem (pItemName char(50),
pItemDescription
char(100),
pX int /*
DEFAULT not possible */,
pY char /* DEFAULT
not possible */,
pZ smallint)
returns INTEGER as
$$
declare
vItemRef integer;
vX integer DEFAULT coalesce(pX,1);
vY char DEFAULT coalesce(pY,'x');
vZ smallint DEFAULT coalesce(pZ,'');
begin
-- Do input error checks
/* checks on vX, vY, vZ */
-- Add the item and return its reference
insert into item (item_name, item_description) values (pItemName,
pItemDescription);
vItemRef := currval('item_item_ref_seq');
return vItemRef;
end
$$
language 'plpgsql';
As you can see there are a lot of changes to make. Not too nice when you are
dealing with hundreds of procedures. This would be greatly easier if:
1) postgres could do DEFAULT parameters.
I know this probably would not happen as it would make functions
harder to recognise and
may not suit postgres polymorphic functions.
2) postgres would allow function parameters to be non-constant without
declaring them as OUT.
I dont want to make them output parameters as they are not output
parameters. I am returning different output parameters. However it would be
great if I could modify (and not pass back) the value of a input parameter.
Perhaps this is *not* allowed for performance reasons ? But it was a huge
factor in the migration and is still causing bother to me as almost every
single parameter needs to be refactored (i.e. put in a coalesce statement
for it and then refactor the name throughout the function body) as almost
every paraemter is DEFAULTED in informix or modified in the informix
function body.
I can't stress how difficult this made the migration. As mentioned there
were hundreds of procedures and some have 40 parameters in them.
Any chance of allowing input parameters to be modified within the function
body ?
_________________________________________________________________
Download the latest version of Windows Live Messenger NOW!
http://get.live.com/en-ie/messenger/overview
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: plpgsql: defuault parameters and constant function parameters
2007-08-28 12:31 plpgsql: defuault parameters and constant function parameters Roger Moloney <ramoloney@hotmail.com>
@ 2007-08-30 00:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-09-18 21:25 ` Re: plpgsql: defuault parameters and constant function parameters Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2007-08-30 00:19 UTC (permalink / raw)
To: Roger Moloney <ramoloney@hotmail.com>; +Cc: pgsql-interfaces
"Roger Moloney" <ramoloney@hotmail.com> writes:
> Having just done the first draft of a large migration from informix to
> postgres, I wanted to point out that the migration was hugely complicated by
> postgres inability to define default parameters
You can get that effect using function name overloading. For instance
create function f(x int, y int) ...
create function f(x int) ... as 'select f(x, 1)' ...
It's easier if the defaultable parameters are at the end, but I rather
wonder how your informix system is disambiguating the calls either
with a signature like that. If you leave off just one of the two
defaultable parameters, how does it know which?
> I dont want to make them output parameters as they are not output
> parameters. I am returning different output parameters. However it would be
> great if I could modify (and not pass back) the value of a input parameter.
Just use a differently named local variable, perhaps? Actually, because
of the scoping rules it seems to me to work fine even if they have the
same name:
regression=# create function ff(f1 int) returns int as $$
declare f1 int := coalesce(f1, 1);
begin
return f1;
end$$ language plpgsql;
CREATE FUNCTION
regression=# select ff(3);
ff
----
3
(1 row)
regression=# select ff(null);
ff
----
1
(1 row)
> Any chance of allowing input parameters to be modified within the function
> body ?
That check is there to prevent mistakes, and I think it's a good one.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: plpgsql: defuault parameters and constant function parameters
2007-08-28 12:31 plpgsql: defuault parameters and constant function parameters Roger Moloney <ramoloney@hotmail.com>
2007-08-30 00:19 ` Re: plpgsql: defuault parameters and constant function parameters Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-09-18 21:25 ` Peter Eisentraut <peter_e@gmx.net>
2007-09-18 22:11 ` Re: plpgsql: defuault parameters and constant function parameters Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 4+ messages in thread
From: Peter Eisentraut @ 2007-09-18 21:25 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Roger Moloney <ramoloney@hotmail.com>
Tom Lane wrote:
> It's easier if the defaultable parameters are at the end, but I
> rather wonder how your informix system is disambiguating the calls
> either with a signature like that. If you leave off just one of the
> two defaultable parameters, how does it know which?
Several other SQL systems allow naming the parameters in calls, such as
CALL foo(a = 1, b = 2)
That's the only way to stay sane with function having 20 or more
parameters anyway.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: plpgsql: defuault parameters and constant function parameters
2007-08-28 12:31 plpgsql: defuault parameters and constant function parameters Roger Moloney <ramoloney@hotmail.com>
2007-08-30 00:19 ` Re: plpgsql: defuault parameters and constant function parameters Tom Lane <tgl@sss.pgh.pa.us>
2007-09-18 21:25 ` Re: plpgsql: defuault parameters and constant function parameters Peter Eisentraut <peter_e@gmx.net>
@ 2007-09-18 22:11 ` Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 4+ messages in thread
From: Bruce Momjian @ 2007-09-18 22:11 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-interfaces@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>; Roger Moloney <ramoloney@hotmail.com>
Peter Eisentraut wrote:
> Tom Lane wrote:
> > It's easier if the defaultable parameters are at the end, but I
> > rather wonder how your informix system is disambiguating the calls
> > either with a signature like that. ?If you leave off just one of the
> > two defaultable parameters, how does it know which?
>
> Several other SQL systems allow naming the parameters in calls, such as
>
> CALL foo(a = 1, b = 2)
>
> That's the only way to stay sane with function having 20 or more
> parameters anyway.
TODO has:
o Allow function parameters to be passed by name,
get_employee_salary(12345 AS emp_id, 2001 AS tax_year)
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-09-18 22:11 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-28 12:31 plpgsql: defuault parameters and constant function parameters Roger Moloney <ramoloney@hotmail.com>
2007-08-30 00:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-09-18 21:25 ` Peter Eisentraut <peter_e@gmx.net>
2007-09-18 22:11 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox