---

Class ::acs::db::postgresql

::acs::db::postgresql create ...

Support class for backend based on PostgreSQL, i.e., the PostgreSQL dialect of SQL). This class can be used with the nsdb and the nsdbi drivers of NaviServer.

Defined in packages/acs-tcl/tcl/acs-db-12-procs.tcl

Class Relations

• class: ::nx::Class
• superclass: ::acs::db::SQL
• subclass: ::acs::db::nsdb-postgresql, ::acs::db::nsdbi-postgresql

::nx::Class create ::acs::db::postgresql \
     -superclass ::acs::db::SQL

Methods (to be applied on instances)

• get_all_package_functions (scripted, public)

   <instance of acs::db::postgresql> get_all_package_functions \
      [ -dbn dbn ]

  PostgreSQL version of obtaining information about the procedures and functions stored in the DB. For PostgreSQL, we keep this in the table "function_args". The information from "acs_function_args" defines, for which functions we want to create an interface. The information is completed with the definitions from the system catalog of PostgreSQL. The resulting list contains entries of the form: sqlpackage object {argument_names ... types ... defaulted ... defaults result_type ....} Currently, "defaults" are only available for PostgreSQL

  Switches:

  -dbn (optional)

  Testcases:

  No testcase defined.

  if {![db_table_exists acs_function_args]} {
      ns_log notice "acs_function_args is not (yet) defined, don't create stub functions now"
      return {}
  }
  
  set definitions [::acs::dc list_of_lists -dbn $dbn dbqd..get_all_package_functions {
      select function, arg_name, arg_default
      from   acs_function_args
      order by function, arg_seq
  }]
  set db_definitions {}
  set last_function ""
  set argument_names {}; set defaulted {}; set defaults {}
  foreach definition $definitions {
      lassign $definition function arg_name default
      if {$last_function ne "" && $last_function ne $function} {
          dict set db_definitions $last_function  [list argument_names $argument_names defaulted $defaulted defaults $defaults]
          set argument_names {}; set defaulted {}; set defaults {}
      }
      lappend argument_names $arg_name
      lappend defaulted [expr {$default eq "" ? "N" : "Y"}]
      lappend defaults $default
      set last_function $function
  }
  dict set db_definitions $last_function  [list argument_names $argument_names defaulted $defaulted defaults $defaults]
  ns_log notice "loaded [dict size $db_definitions] definitions from function args"
  
  #
  # Get all package functions (package name, object name,
  # argument types, return type) from PostgreSQL system
  # catalogs.
  #
  set pg_data [::acs::dc list_of_lists -dbn $dbn dbqd..[current method] {
      select distinct
      af.function,
      substring(af.function from 0 for position('__' in af.function)) as package_name,
      case when position('__' in af.function)>0 then substring(af.function from position('__' in af.function)+2) else af.function end as object_name,
      array_to_string(proargnames, ' '),
      oidvectortypes(proargtypes),
      format_type(prorettype, NULL)
      from pg_proc, acs_function_args af
      where proname = lower(af.function)
  }]
  
  foreach item $pg_data {
      lassign $item key package_name object_name proargnames argument_types result_type
      #ns_log notice "got from db" key $key package_name $package_name object_name $object_name
      set argument_types [lmap argument_type [split $argument_types ,] {
          string trim $argument_type
      }]
      set function_arg_names [string tolower [dict get $db_definitions $key argument_names]]
      set nr_defined_args [llength $function_arg_names]
      if {[llength $argument_types] < $nr_defined_args} {
          #
          # This might be a definition with fewer arguments; we
          # aim always for the definition with the most
          # arguments.
          #
          #ns_log notice "key $key has argument_types [llength $argument_types] but this is less than nr_defined_args $nr_defined_args"
          continue
      } elseif {[llength $argument_types] < $nr_defined_args} {
          ns_log warning "generate_stubs: $key has less arguments in "  "function_definitions ($nr_defined_args) than in DB [llength $argument_types]"
          ns_log notice ".... have already types [dict exists $db_definitions $key types]"
          continue
      }
  
      if {$proargnames eq ""} {
          ns_log warning "$key /$nr_defined_args has no argument names in DB. "  "Names should match <$function_arg_names>"
      } elseif {[llength $proargnames] > $nr_defined_args} {
          #
          # In case a function returns tuples from the DB, the
          # name of the attributes of these tuples are also
          # returned in proargnames from PostgreSQL. Just take
          # the names, for which we have types.
          #
          set proargnames [lrange $proargnames 0 $nr_defined_args-1]
          #ns_log notice $key FIXED proargnames <$proargnames>
      }
  
      # if {$key eq "CONTENT_ITEM__TRASH_RECOVER_SINGLE_ITEM"} {
      #     ns_log notice "$key /$nr_defined_args, package_name: '$package_name'"  #         function_arg_names <$function_arg_names> \n  #         db_names <$proargnames> \n  #         db_types <$argument_types>
      # }
  
      if {![:argument_name_match  -key $key  -function_arg_names $function_arg_names  -db_names $proargnames]} {
          continue
      }
  
      if {[dict exists $db_definitions $key types]} {
          ns_log warning "$key /$nr_defined_args, package_name: '$package_name' ignoring duplicate function"  function_arg_names <$function_arg_names> \n  db_names <$proargnames> \n  db_types <$argument_types>  have already <[dict get $db_definitions $key types]>
  
          continue
      }
      dict set db_definitions $key result_type $result_type
      dict set db_definitions $key types $argument_types
      dict set db_definitions $key package_name $package_name
      dict set db_definitions $key object_name $object_name
  }
  return [lmap {key entry} $db_definitions {
      if {![dict exists $entry package_name]} {
          #
          # When we have an entry in acs_function_args, but no
          # such function in the database, complain.
          #
          ns_log warning "missing DB for $key: <$entry>"
          continue
      }
      list [dict get $entry package_name] [dict get $entry object_name] $entry
  }]
  return $db_definitions

• map_function_name (scripted, public)

   <instance of acs::db::postgresql> map_function_name sql

  Replace calls to function names in provided SQL (dummy function for PostgreSQL)

  Parameters:

  sql (required)

  Testcases:

  No testcase defined.

  return $sql

---