Forum OpenACS Q&A: How to insert more than 16 values into a table of postgresql, using functions

1: How to insert more than 16 values into a table of postgresql, using functions

Posted by VenuMadhav Deevi on 07/12/03 09:19 AM

Hi,
I am having a table of 25 fields in PostgreSQL which cannot be normalized. I had created a function called as xxx_new which i am using it from the openACS for inserting the new values from the values given in widget which is displayed on the browser. My problem is that when this function is called and trying to insert the values it is returning an error saying that postgresql function cannot accept parameters more than 16.
How to insert these many values into the table plz let me know is there any mechanism to solve this problem.

This the problem so plz try to give me some solution to this as this is my immediate task.

With Regards
Venu Madhav Deevi.

2: Re: How to insert more than 16 values into a table of postgresql, using functions (response to 1)

Posted by Jarkko Laine on 07/12/03 11:18 AM

Like I told you in a previous thread, you can change the maximum number of arguments when compiling PostgreSQL. Search for 'FUNCTION_MAX_ARGS' in pg_config.h.in in your postgres source folder under src/include/. If I remember correct, PG7.3 doesn't have this restriction anymore, but it might be a misconception.

If you're not able to recompile, you can maybe use two different plpgsql functions and call them both from a single tcl proc. You'd then do the insert in the first function and return the key of the row. In the second function you'd then modify the row using the key you just got. Don't know if this is the best way (or a good idea at all), though, just one possible solution.

3: Re: How to insert more than 16 values into a table of postgresql, using functions (response to 2)

Posted by VenuMadhav Deevi on 07/12/03 01:11 PM

Hi Jarkko Laine,
I will try to do the first solution i.e changing the config file. Is it available with PostGreSQL 7.2.4 version, as i am using this as my database.
One more doubt about the second solution that how to get the "key" of the row from the function i tried with the ACS API called as db_exec_plsql but i could not able to get the return value from the function after the execution of function xxx_new. Can u plz tell me how to do this in a small sample way.

I once thank you for your answere, hoping to get solution from you in a early way.

Regards

Venu Madhav Deevi

4: Re: How to insert more than 16 values into a table of postgresql, using functions (response to 3)

Posted by Jarkko Laine on 07/12/03 04:22 PM

Yes, the file should be there. Just check.

The difference between function and procedure in plpgsql is that a function has a return value. If you say "select plpgsql_func(...)" in db_exec_plsql block, you will get the key, as long as the plpgsql function returns the appropiate key.

You can even use sequence pooling and db_nextval to get the key so you don't even have to grab the value from plpgsql. See OpenACS DB Access API Guide for this.

Example:

set key [db_nextval yoursequence]

db_exec_plsql procname "select procname($key, ...(other attributes)...)"
db_exec_plsql proc2name "select proc2name($key, ...(rest of the attributes)...)"

Hope this helps.

5: Re: How to insert more than 16 values into a table of postgresql, using functions (response to 1)

Posted by Randy O'Meara on 07/12/03 07:53 PM

There is an excellent forum thread that describes the "update after new" method to get around the postgres (<7.3) pl 16 arg limit.

Look here: https://openacs.org/forums/message-view?message_id=91910

Randy