Forum OpenACS Development: Porting pl/pgsql using PERFORM and SELECT to call functions

A function in pl/pgsql is calling another function. I need to use the
PERFORM or SELECT. It looks like this is unnecessary in Oracle.

I didn't see this in the Porting from Oracle PL/SQL doc or the
porting guide here.

I need the resulting object_id from one object.new query to use in a following query. From psql the following works:

select product__new(NULL,
'4734',
'CDRW15',
'Some Description',
'Some Longer Description',
'1',
'product',
now(),
null,
null,
null) as product_id from dual

The above query is generated by the query processor, just pasted into psql.

The select my_func() into product_id method suggested in a previous post doesn't return any rows in psql, or in the db_* procedures.

Is there a method of doing this so that I can get the product_id defined for the next db_exec_plsql procedure?

That sounds like a great way to do it, thanks! The package I am working on is a new ecommerce package. Hopefully it will make less assumptions about how to sell, and use the workflow package to push items through the system. It is also more oriented toward repeat customers. Instead of a 'shopping cart', the system uses a more perminent 'order template' or purchase order. This also kind of doubles as a bookmarking system. Also, like real vendors, the system supports multiple suppliers of the same product.

A new ecommerce is sorely needed, though the modernized version of ecommerce3 with the service contract-based hookup to payment gateways will be a nice stopgap.  You'll want to use these payment gateways, as Bart mentions.

I've worked with acs-workflow quite a bit over the past three weeks, ping me if you want some information on gotchas and hangups and the like.  Short story: it's a great first cut and a sound foundation, but the current implementation has some (relatively minor) annoyances and shortcomings (many of the shortcomings noted on the aD "TODO" list for the package).  Neophytos and I are both interested in improving it as time rolls on by ...

Using workflow is the Right Thing To Do but I might be able to save you a little time.  Also I've been steadily committing a series of minor bugfixes to the package as I've been working with it.  I've fixed the bugs I've hit as I've rolled on.

Another thing to look at is the form handler in the template system.  It's very useful.  I recently fixed the "currency" widget and datatype (it was only about 30% done in ACS 4.2 classic, apparently).

It's not tied into acs-lang but it should be possible to do so, and this seems the right way to make an ecommerce package know about different currencies.  acs-lang ties formatting information to locales, and the form handler's widget is capable of dealing with specified formats, so off we go, right?  (semantics, like figuring out how much an item costs in US$ vs. Euros is a [*cough*] simple exercise left for the reader).

I was able to clean up the requirements doc a little, so you can read more about what I am working on at http://stellardata.com:7777/ms/doc/requirements-2.html

The 'extension' to the query dispatcher allow me to take an unported oracle plsql code block like the following:

  declare
   v_product_id integer;
  begin
   v_product_id := product.new(
     name => :name,
     short_description => :short_description,
     long_description => :long_description,
     mfg_id => :mfg_id,
     product_state => :product_state
   );
  end;

and replace it with:

select [qd_write_query_select product__new {
 name => :name
 short_description => :short_description
 long_description => :long_description
 mfg_id => :mfg_id
 product_state => :product_state
}]

I think my cronjob package has all the procs in it in the cronjob-init.tcl file. But essentially you 'register a package' like this:

qd_add_package product__new product_id mfg_id name short_description long_description product_state object_type creation_date creation_user creation_ip context_id

Probably this should be renamed, as pg doesn't have packages. but actually the function prototype is product__new and what follows is a list of all the attributes that you want to be able to change through this function prototype. The qd_add_package procedure assigns values to each of the following attributes. the first one is assigned the value of 1, the second is assigned the value of 2, the third, 4, etc.

Then to actually register a real pg function, you call the procedure:

qd_add_function product__new "product_id" "NULL" "mfg_id" "" "name" "" "short_description" "" "long_description" "" "product_state" "1" "object_type" "'product'" "creation_date" "now()" "creation_user" "null" "creation_ip" "null" "context_id" "null" 

In this case, there exists an actual function product__new with the following attributes. Each attribute is followed by a default value. If no default is given "", then it must be included when you call the qd_write_query_select procedure. When this procedure is called, it totals up the values of the following list of attributes, something I call a signature. The procedure then compares this signature with each registered function named 'product__new'.

The result is three things:

• It takes about 30 sec to port a code block.
• It works with overloaded functions, picking the first one that matches.
• Something else that presently slips my mind.

The result of the query writing procedure is something like this:

select product__new(NULL,
'4734',
'CDRW15',
'Acer CD-RW 32/12/8',
'Acer CD-RW 32/12/8',
'1',
'product',
now(),
null,
null,
null) 

Which ends up in the querytext tag.

Thanks Neophytos, but I made a mistake.

The actual result of the query writing procedure above is actually just the string:

product__new(NULL,
:mfg_id,
:name,
:short_description,
:long_description,
:product_state,
'product',
now(),
null,
null,
null);

So the additional benefit of this method is that if the pg function changes, you may only have to change the call to the qd_add_* procedures.