db_exec_plpgsql (private)

 db_exec_plpgsql db statement_name pre_sql fname

Defined in packages/acs-tcl/tcl/01-database-procs.tcl

PostgreSQL only.

A helper procedure to execute a SQL statement, potentially binding depending on the value of the $bind variable in the calling environment (if set).

Low level replacement for db_exec which replaces inline code with a proc. db proc is dropped after execution. This is a temporary fix until we can port all of the db_exec_plsql calls to simple selects of the inline code wrapped in function calls.

emulation of plsql calls from oracle. This routine takes the plsql statements and wraps them in a function call, calls the function, and then drops the function. Future work might involve converting this to cache the function calls

This proc is private - use db_exec_plsql instead!

Parameters:

db (required)

statement_name (required)

pre_sql (required)

fname (required)

See Also:

• db_exec_plsql

Partial Call Graph (max 5 caller/called nodes):

Testcases:

No testcase defined.

Source code:

    set start_time [expr {[clock clicks -microseconds]/1000.0}]

    set sql [db_qd_replace_sql -ulevel 3 $statement_name $pre_sql]

    set unique_id [db_nextval "anon_func_seq"]

    set function_name "__exec_${unique_id}_${fname}"

    # insert Tcl variable values (OpenACS - Dan)
    if {$sql ne $pre_sql } {
        set sql [uplevel 2 [list subst -nobackslashes $sql]]
    }
    ns_log Debug "PLPGSQL: converted: $sql to: select $function_name ()"

    # create a function definition statement for the inline code
    # binding is emulated in tcl. (OpenACS - Dan)

    set errno [catch {
        upvar bind bind
        if { [info exists bind] && [llength $bind] != 0 } {
            if { [llength $bind] == 1 } {
                set proc_sql [db_bind_var_substitution $sql [ns_set array $bind]]
            } else {
                set proc_sql [db_bind_var_substitution $sql $bind]
            }
        } else {
            set proc_sql [uplevel 2 [list db_bind_var_substitution $sql]]
        }

        ns_db dml $db "create function $function_name () returns varchar as [::ns_dbquotevalue $proc_sql] language 'plpgsql'"

        set ret_val [ns_db 0or1row $db "select $function_name ()"]

        # drop the anonymous function (OpenACS - Dan)
        # JCD: ignore return code -- maybe we should be smarter about this though.
        catch {ns_db dml $db "drop function $function_name ()"}

        return $ret_val

    } error]

    set errinfo $::errorInfo
    set errcode $::errorCode

    ds_collect_db_call $db 0or1row $statement_name $sql $start_time $errno $error

    if { $errno == 2 } {
        return $error
    } else {
        catch {ns_db dml $db "drop function $function_name ()"}
    }

    return -code $errno -errorinfo $errinfo -errorcode $errcode $error

XQL Not present:

Generic, PostgreSQL, Oracle