Skip site navigation (1) Skip section navigation (2)

plpgsql: defuault parameters and constant function parameters

From: "Roger Moloney" <ramoloney(at)hotmail(dot)com>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: plpgsql: defuault parameters and constant function parameters
Date: 2007-08-28 12:31:57
Message-ID: BAY115-F27A40D39CE453718AA0380AFD30@phx.gbl (view raw or flat)
Thread:
Lists: 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


Responses

pgsql-interfaces by date

Next:From: Brijesh ShrivastavDate: 2007-08-28 17:10:21
Subject: Re: Issue with PQdescribePortal to describe a select cursor
Previous:From: Tom LaneDate: 2007-08-28 00:04:29
Subject: Re: Issue with PQdescribePortal to describe a select cursor

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group