Forum OpenACS Q&A: pgplsql and output parameters

Collapse
1: pgplsql and output parameters
Posted by John Sequeira on 10/05/01 07:12 PM
I am working with the acs-workflow module,  and running into a limitation with Postgresql pgplsql and output parameters.  It looks like pgplsql doesn't support this,  and there are several areas where the acs-workflow procedures rely on passing back multiple values. I couldn't find any docs to confirm this,  but the sql code has comments saying :

TODO: output parameters

Are they not supported,  and if so is there a preferred workaround?

(my guesses are writing to a temp table and returning it's name (ugh) or returning a record type?)

Collapse
2: Response to pgplsql and output parameters (response to 1)
Posted by Don Baccus on 10/06/01 12:13 AM
In acs-workflow I only see a case labelled "FIXME" that refers to a callback used to set up some things for e-mail notifications.  Do the existing callbacks actually make use of this feature?  For something as limited as this we'd probably just want to define additional function callbacks to be used to get the party_from id, etc, but I'll defer to Dan and Vinod for more specific information.

BTW you should probably be asking this in the OpenACS 4 design forum - I'm not sure either Dan or Vinod monitor the general forum (though they might, mind you!)

Collapse
3: Response to pgplsql and output parameters (response to 1)
Posted by Dan Wickstrom on 10/08/01 03:43 PM
A good way to fix this would be to just move the notification call inside the callback. This would alleviate the need to pass the callback parameters back out of the callback function. Currently the workflow_case__notify_assignee is the only one that makes use of the notify_assignee__callback. The structure is as follows: 
        -- FIXME: last three args are also out varibles.

        if notify_assignee__callback != '''' and notify_assignee__callback is not null then
            v_str :=  ''select '' || notify_assignee__callback || ''('' || 
                      notify_assignee__task_id || '','' ||
                      coalesce(quote_literal(notify_assignee__custom_arg),''null'') || 
                      '','' ||
                      notify_assignee__user_id || '','' ||
                      v_party_from || '','' ||
                      quote_literal(v_subject) || '','' ||
                      quote_literal(v_body) || '')'';

            execute v_str;
        end if;
            
        v_request_id := acs_mail_nt__post_request (       
            v_party_from,                 -- party_from
            notify_assignee__user_id,     -- party_to
            ''f'',                        -- expand_group
            v_subject,                    -- subject
            v_body,                       -- message
            0                             -- max_retries
        );

If nobody has an objections, I can move remove the acs_mail_nt__post_request call and leave a comment here that we should include the call in any callback functions that we write. I also could modify the sample code so that it incorporates these changes.

Collapse
4: Response to pgplsql and output parameters (response to 1)
Posted by Don Baccus on 10/08/01 09:35 PM
That would be excellent - I assume (of course) that the change would be mirrored in the Oracle version, too, right?
Collapse
5: Response to pgplsql and output parameters (response to 1)
Posted by Dan Wickstrom on 10/08/01 11:14 PM
Yes, for consistency, I can change both the oracle and the postgresql versions.
Collapse
6: Response to pgplsql and output parameters (response to 1)
Posted by John Sequeira on 10/09/01 03:40 PM
Dan,

At the risk of getting further into design (sorry Don),  I wouldn't remove the call completely.  It's pretty useful default functionality to get an email when you've been assigned a task.

I'm not sure what the best way would be to preserve this:  it would be great if the return value of the callback could be used to determine whether it had sent the message,  but I don't know if that's a bad convention, or how well supported it would be across platforms.

FWIW, in another comment I saw in the code,  Lars (?) suggested that notification messages would ideally be made from Tcl.  This is simpler for a couple of reasons, but mainly that the Tcl code can construct an HTML notification message with subsite URLs easier.  The HTML that you send out in the email will likely be reused in the workflow status pages (or vice versa).

Collapse
7: Response to pgplsql and output parameters (response to 1)
Posted by Don Baccus on 10/09/01 04:19 PM
Dan wasn't talking about removing but rather moving the call that posts a response.  Still in the DB though.  It might make more sense to move the creation of the response to Tcl, though, as Lars was thinking.  It could then call the acs_mail routine to do the post after building a better message.
Collapse
8: Response to pgplsql and output parameters (response to 1)
Posted by Dan Wickstrom on 10/09/01 06:43 PM
The newer 4.3 revision of workflow might have moved notifications into the tcl layer, but I haven't checked.

Neophytos has volunteered to port the newer 4.3 version of workflow, so we might see this fixed when he's done with the porting effort.  If not, maybe Neophytos will fix the notifications problem while he's porting the workflow package.

Until then, I've moved the notifications call into the callback routine.