Thread: Number or parameters for functions - limited to 32 ?
I am attempting to port and application that was designed for MS-SQL to Postgres and bumped into a limitation of pg functions, namely that pg functions can only support 32 parameters. Our app takes records from a only cobol file(s) and populates an SQL table(s), such that one table in the db = one file from the file system. With MS-SQL we use a stored procedure to handle the update/inserts and pass the fields information as parameters to the procedure as part of "EXECUTE" command. Sometimes there can be as many as 50+ parameters to be passed. Typically pg functions as offered as the equivalent to "store procedures", however, in this case seems to be a limitation. If anyone has any other suggestions, we would appreciate some input.
imageguy wrote: > I am attempting to port and application that was designed for MS-SQL > to Postgres and bumped into a limitation of pg functions, namely that > pg functions can only support 32 parameters. I thought it had been increased to 64, but I'm sure you're right. > Our app takes records from a only cobol file(s) and populates an SQL > table(s), such that one table in the db = one file from the file > system. > > With MS-SQL we use a stored procedure to handle the update/inserts and > pass the fields information as parameters to the procedure as part of > "EXECUTE" command. Sometimes there can be as many as 50+ parameters > to be passed. Ah, but it's a record, so pass a record type in: CREATE TYPE cobol_file_type AS (a integer, b text, c varchar, d date); CREATE FUNCTION build_tables1(c cobol_file_type)... Each existing table has its own type defined too (with the same name), which might save you some time. -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes:
> imageguy wrote:
>> I am attempting to port and application that was designed for MS-SQL
>> to Postgres and bumped into a limitation of pg functions, namely that
>> pg functions can only support 32 parameters.
> I thought it had been increased to 64, but I'm sure you're right.
It was raised to 100 in 8.1.
regards, tom lane
Just a FYI, you can get around the 32 param limit in older versions of postgresql by passing the function a varchar/text array, then use the built in array functions to pull the "params" from the passed array. Something like this(not tested code, just a sample): CREATE or REPLACE FUNCTION test_func(varchar) RETURNS void AS $BODY$ DECLARE IN_ARRAY text[] ; ACCOUNTNUMBER_IN varchar; BEGIN IN_ARRAY = string_to_array($1,'~^~'); --use a unique delimiter ACCOUNTNUMBER_IN = IN_ARRAY[1]; return; END $BODY$ LANGUAGE 'plpgsql' VOLATILE; Later, Tony Caduto AM Software Design Home of Lightning Admin for PostgreSQL and MySQL http://www.amsoftwaredesign.com
On Apr 8, 12:20 pm, t...@sss.pgh.pa.us (Tom Lane) wrote: > Richard Huxton <d...@archonet.com> writes: > > imageguy wrote: > >> I am attempting to port and application that was designed for MS-SQL > >> to Postgres and bumped into a limitation of pg functions, namely that > >> pg functions can only support 32 parameters. > > I thought it had been increased to 64, but I'm sure you're right. > > It was raised to 100 in 8.1. > > regards, tom lane Thanks Tom. - forgive me for asking but where would I find this in the docs ? Richard - thanks for the tip on the record type
imageguy <imageguy1206@gmail.com> writes:
> On Apr 8, 12:20�pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
>> It was raised to 100 in 8.1.
> Thanks Tom.
> - forgive me for asking but where would I find this in the docs ?
Under the max_function_args parameter, or in the 8.1 release notes ...
regards, tom lane
> Ah, but it's a record, so pass a record type in: > > CREATE TYPE cobol_file_type AS (a integer, b text, c varchar, d date); > CREATE FUNCTION build_tables1(c cobol_file_type)... > > Each existing table has its own type defined too (with the same name), > which might save you some time. > > -- > Richard Huxton > Archonet Ltd This was exactly the solution that worked. We used the tables record type. Thanks again for the tip. g.