Number or parameters for functions - limited to 32 ?

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.