Forum OpenACS Development: Is package_exec_plsql able to handle multiple definitions of the same function (PG)?

1: Is package_exec_plsql able to handle multiple definitions of the same function (PG)?

Posted by Rocael Hernández Rizzardini on 01/06/05 07:18 PM

First we found a trouble with content::template::new, basically didn't perform the expected result.
Searching more, for instance, we found that in
packages/acs-content-repository/sql/postgresql/content-template.sql
if you define this:
select define_function_args('content_template__new','name,parent_id,template_id,creation_date,creation_user,creation_ip,text,is_live');
make to work well content::template::new, rather than with the original definition:
select select define_function_args('content_template__new','name,parent_id,template_id,creation_date,creation_user,creation_ip');

Looking at package_exec_plsql tcl function, seems that do not supports correctly the use of multiple PG functions with the same name, like in this case content_template__new has 4 different definitions, and the only distinction is by the params (as it is in PG). Or package_exec_plsql supports well multiple PG function definition only if:
1. The order of the params among the different PG functions preserve the order, i.e.:
you can have:
my_func (var1, var2, var3, var4)
and
my_func (var1, var3, var4)

but not
my_func (var1, var2, var3, var4)
and
my_func (var1, var4, var2)

2. The caller to package_exec_plsql do not set in the -var_list flag items values as "" that weren't passed (as happens on content::template::new that the optional params are sent to package_exec_plsql with the value "", instead of not sending them at all)

Maybe I'm missing something with package_exec_plsql, but if my statement is true, or we do need a convention to define multiple PG functions with the same (meaning preserving the params "order"), or we need a hack to support those withouth caring about the order. And if we support the "order" convention we might need to review the define_function_args calls to verify that they are defined using the PG function that has the larger number of params.

2: Re: Is package_exec_plsql able to handle multiple definitions of the same function (PG)? (response to 1)

Posted by Dave Bauer on 01/06/05 07:34 PM

Package exec plsql is not supposed to support multiple define_function_args.

Remember the point of package_exec_plsql is to call the excat same procedure in oracle and postgresql. So the postgresql plpgsql function that is called needs to have the same parameters as the oracle version.

You can not have multiple plpgsql functions with the same number of params with OpenACS since the bind variable emulation turns most parameters into strings by quoting them. So in this case postgresql can only guess the correct function based on the number of parameters.

What you need to do is create a content_template__new that is exactly the same as the oracle version with all the same parameters. If you need to add an OPTIONAL parameter to both Oracle and PG, then you need to create a new PG function with more parameters and remove the old define function args call and create a new one for the function with the new parameter.

5: Re: Re: Is package_exec_plsql able to handle multiple definitions of the same function (PG)? (response to 2)

Posted by Rocael Hernández Rizzardini on 01/06/05 07:52 PM

I think PG knows which function is being called based on the number and type of the params.

Also, then, package_exec_plsql cannot handle 2 or more function calls well unless there's a convention for the params. And I don't know exactly why there are multiple definitions of content_template__new while in oracle is just one .new.

6: Re: Re: Re: Is package_exec_plsql able to handle multiple definitions of the same function (PG)? (response to 5)

Posted by Dave Bauer on 01/06/05 07:59 PM

The reason is so that its "easier" to call postgresql functions. For example content_item__new is up to 19 parameters last time I checked. There is a simplified version with 4 or 5 parameters that maybe use useful.

Remember that previous to package_exec_plsql and the drive to move most functions up into Tcl procedures there were many calls to plsql functions in XQL files. Now that we have package_exec_plsql and are moving more functions into Tcl procs, we don't really need the overloaded functions.

So to summarize, only the function that matches the oracle version should have define_function_args called for it. I fixed this for content_template__new on oacs-5-1 and HEAD with an upgrade script.

7: Re: Re: Re: Re: Is package_exec_plsql able to handle multiple definitions of the same function (PG)? (response to 6)

Posted by Rocael Hernández Rizzardini on 01/06/05 08:20 PM

I see the reason, then we can gradually get rid of those mini-functions, also, there are problems with __del as well. Thanks for the help!

3: Re: Is package_exec_plsql able to handle multiple definitions of the same function (PG)? (response to 1)

Posted by Dave Bauer on 01/06/05 07:39 PM

Also I am reviewing the code to make sure the package_exec_plsql calls will properly work with the parameters that have defined defaults so that if an optional paramter is not specified it will not be sent to package_exec_plsql as an empty string and the default will be set correctly.

I have reviewed the ::new functions so far. This is updated on oacs-5-1 at least.

4: Re: Is package_exec_plsql able to handle multiple definitions of the same function (PG)? (response to 1)

Posted by Dave Bauer on 01/06/05 07:44 PM

Ok, out of your message, I figured out the problem. Define_function_args was just on the wrong version. I fixed it and made an upgrade script for oacs-5-1 branch.