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

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.