Postgres Pro
Downloads
Home   >   mailing lists

Thread: maximum parameters limit to function & manipulating array in plpgsql

maximum parameters limit to function & manipulating array in plpgsql

From
"Advid@newgen"
Date:
Hi,
 
I have 2 major problems bothering me and probably u are the best person to help me out :-
 
1)
Well I read one of ur solutions to sending more tham 16 parameters to a function in plpgsql. I have changed the value of FUNC_MAX_ARGS (a/k/a INDEX_MAX_KEYS) in \usr\local\plpgsql\include\config.h after stoping the postmaster service.
 On restarting the service after having changed config.h I am atill experiencing the same problem. What should I do now?
 
2)
Secondly I am trying to use arrays as below :
 
create function ftest(integer[]) returns integer[] as'
declare
n $1%type;
begin
    .
    ...
    n:={1,12,11};
    return n;
end;
'language 'plpgsql';
 
If I declare n as integer[3] it gives a compilation error. So I tried it as above. Can I do any array manipulations at all in plpgsql like assigning,copying etc values in to other arrays in plpgsql. If so how ??
 
Do help me out since I have got stuck with both these problems.
 
Regards,
Advid 
Newgen Software Tech

Re: maximum parameters limit to function & manipulating array in plpgsql

From
"Josh Berkus"
Date:
Advid,

> Well I read one of ur solutions to sending more tham 16 parameters to
> a function in plpgsql. I have changed the value of FUNC_MAX_ARGS
> (a/k/a INDEX_MAX_KEYS) in \usr\local\plpgsql\include\config.h after
> stoping the postmaster service.
>  On restarting the service after having changed config.h I am atill
> experiencing the same problem. What should I do now?

Did you re-init PostgreSQL, and then re-build your database from a
pgdump file?  I believe that these steps are necessary as well.

> If I declare n as integer[3] it gives a compilation error. So I tried
> it as above. Can I do any array manipulations at all in plpgsql like
> assigning,copying etc values in to other arrays in plpgsql. If so how
> ??

No, you can't.  Array support in PL/pgSQL is quirky and not all the way
there.  Jan Wieck has instead been focusing on building CURSOR support
into PL/pgSQL so arrays are likely to be unevenly supported for some
time unless somebody else takes on the task.  Mind you, there have been
some proponents of dropping the array data type entirely as not
relational (it isn't) so I don't know where this feature is going.

Basically, you can declare an array as a parameter of a function, but
that's it.  You cannnot have array variables or retuurn types.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco
Attachment