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

I am running into this:

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.

There are (at least) 3 ways to call a function from within another function in plpgsql.

1) v_foo := my_func();
2) select my_func() into v_foo;
3) perform my_func();

The only difference between a select and a perform is that perform doesn't capture the return value. Also, it should be noted that perform is a plgpsql-only thing - it won't work outside of a stored-procedure block.

Just calling 'my_func()' will work in oracle, but in pg you need to stick a 'perform' in front of it

And there's also EXECUTE, for dinamyc queries inside PL/pgSQL.
But *only* use EXECUTE for dynamic SQL - a queryplan is always generated when the EXECUTE is performed.  If you use PERFORM or SELECT the queryplan is generated only once per backend instance, which is about 1/3 faster for simple queries.  At least, this was the performance difference measured by one benchmarker who measured query performance a few months ago.

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

select product__new(NULL,
'Some Description',
'Some Longer Description',
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?

Tom, you can get the next object_id by calling db_nextval acs_object_id_seq. Pass this object_id to product__new and your new product will have the object_id as product_id.

This is how it works in the ecommerce package for ec_product__new. From your post it is not clear wether you mean a different product__new procedure or not.

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.

Very interesting Tom. You might or might not know that Janine and I are working on a service contract for credit card acceptance. Janine will produce a implementation for Verisign while I will write an implementation. You could make use of them or add a new gateway to these two if you are using a different one.

My personal intention is to bring the ecommerce package up to date and use it for our import and distribution company. Your work sounds like a better way to go about ecommerce than the 3.x version, though. Could I offer any help? As a tester or co-developer maby?

Tom ... the "into" technique works within PL/pgSQL (or, in Oracle, in PL/SQL) only.  You use it to put a value into a PL/[pg]SQL variable, not to propagate the value to the outside (Tcl) world.

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).

Bart & Don:

I would not mind having some help, advice or even just moral support! I have no illusions that this ecommerce module will have as much 'stuff' in it as the 3.x version, but that is the point. I have used the 3.x version for several years, and it is amazing how many things I have had to remove and add to the current system for my customers. 3.x is usually easy to fix, but it is always a hack.

Right now I am working on the admin pages. One thing I want to ensure is that the vendor should use the site to place orders if the customer calls by phone (80% of the time in this case). Another feature that I need to support is the ability of customers to add their own products (private products), which the vendor will source.

Gotta run for now, more later.

I was able to clean up the requirements doc a little, so you can read more about what I am working on at

Tom, the metadata implementation (an extension to the query dispatcher I assume?) in the tcl directory looks interesting. Could you tell us a few things about it?

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

   v_product_id integer;
   v_product_id :=
     name => :name,
     short_description => :short_description,
     long_description => :long_description,
     mfg_id => :mfg_id,
     product_state => :product_state

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,
'Acer CD-RW 32/12/8',
'Acer CD-RW 32/12/8',

Which ends up in the querytext tag.

Thanks Tom. It seems like a good, simple and clear persistence layer.

Thanks Neophytos, but I made a mistake.

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


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.