Thread: create function: number of parameters

Hi,


although I've been using databases for more than ten years by now, I am
rather new to postgres and stumbled upon a problem. I tried to isolate all
the application's logic dealing with data by putting all input and output
into functions.

So instead of writing complex insert/update statements, for inserting a
new record, I just want to call a function with the necessary
parameters. For example, I want to create a new customer, by calling:

SELECT create_customer('name', 'christian name', 'street', 'mailbox',
'location', 'phone number');

The function "create_customer" deals with the data and updates several
tables accordingly. My problem is, that postgres tells me I can't use more
than 16 parameters. For those basic "create" functions I need more than 40
parameters. Is there any easy way to accomplish this or do you have any
good hints for me?


Best regards
... Ralph ...

On Tue, Jul 16, 2002 at 12:23:16AM +0200, Ralph Graulich wrote:
> My problem is, that postgres tells me I can't use more
> than 16 parameters. For those basic "create" functions I need more than 40
> parameters. Is there any easy way to accomplish this or do you have any
> good hints for me?

You can edit FUNC_MAX_ARGS in src/include/pg_config.h, recompile and
re-initdb.

There has been some talk of raising the default setting, but no one
has yet stepped forward with any data on the performance implications
of doing that (or a way to reduce the performance hit, if there is one).

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Hi Neil,


> You can edit FUNC_MAX_ARGS in src/include/pg_config.h, recompile and
> re-initdb.

Being a rather simple task I just edited the header file as you described,
recompiled and installed postgreSQL 7.2.1. For testing, if raising the
limit works at all, I created a dummy function:

CREATE OR REPLACE FUNCTION
  create_customer(
  INTEGER, VARCHAR, VARCHAR, VARCHAR, VARCHAR,
  INTEGER, VARCHAR, VARCHAR, INTEGER, INTEGER,
  INTEGER, VARCHAR, VARCHAR, VARCHAR, VARCHAR,
  VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR,
  VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR,
  VARCHAR, VARCHAR, VARCHAR, VARCHAR)
  RETURNS INTEGER AS '
BEGIN
  RETURN 1;
END;
' LANGUAGE 'plpgsql'

I used the function by calling:

SELECT create_customer(
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL);

Seems to work as it returns 1 (1 row). So I will step further into it and
assemble the code for the function body now and check thoroughly wether
all the parameters get handled oorrectly.

Thanks for your immediate and accurate assistance!


Kind regards
... Ralph ...