PLRuby::PL::Plan (Class)

In: plruby.rb
Parent: Object

class for prepared plan

Methods

cursor   each   exec   execp   execp   fetch   fetch   new   release   save  

Public Class methods

Prepares a query plan for later execution.

If the query references arguments, the type names must be given as a Ruby array of strings.

If the hash given has the keys output, count these values will be given to the subsequent calls to each

If "save" as a true value, the plan will be saved

Public Instance methods

Create a new object PL::Cursor

If output is specified it can take the values

  • "array" return an array with the element ["name", "value", "type", "len", "typeid"]
  • "hash" return an hash with the keys {"name", "value", "type", "len", "typeid"}
  • "value" return an array with all values

If there was a typelist given to PL::Plan::new, an array of values of exactly the same length must be given to PL::Plan#cursor

Same then exec but a call to SPI_cursor_open(), SPI_cursor_fetch() is made.

Can be used only with a block and a SELECT statement

   create function toto() returns bool as '
          plan = PL::Plan.new("select * from T_pkey1")
          warn "=====> ALL"
          plan.each do |x|
             warn(x.inspect)
          end
          warn "=====> FIRST 2"
          plan.each("count" => 2) do |x|
             warn(x.inspect)
          end
          return true
   ' language 'plruby';

   plruby_test=# select * from T_pkey1;
    skey1 | skey2 | stxt
   -------+-------+------
       12 | a     | b
       24 | c     | d
       36 | e     | f
   (3 rows)

   plruby_test=#
   plruby_test=# select toto();
   NOTICE:  =====> ALL
   NOTICE:  {"skey1"=>"12", "skey2"=>"a", "stxt"=>"b"}
   NOTICE:  {"skey1"=>"24", "skey2"=>"c", "stxt"=>"d"}
   NOTICE:  {"skey1"=>"36", "skey2"=>"e", "stxt"=>"f"}
   NOTICE:  =====> FIRST 2
   NOTICE:  {"skey1"=>"12", "skey2"=>"a", "stxt"=>"b"}
   NOTICE:  {"skey1"=>"24", "skey2"=>"c", "stxt"=>"d"}
    toto
   ------
    t
   (1 row)

   plruby_test=#

Execute a prepared plan from PL::Plan::new with variable substitution. The optional count value tells PL::Plan#exec the maximum number of rows to be processed by the query.

If there was a typelist given to PL::Plan::new, an array of values of exactly the same length must be given to PL::Plan#exec as first argument. If the type list on PL::Plan::new was empty, this argument must be omitted.

If the query is a SELECT statement, the same as described for PL#exec happens for the loop-body and the variables for the fields selected.

If type is specified it can take the values

  • "array" return an array with the element ["name", "value", "type", "len", "typeid"]
  • "hash" return an hash with the keys {"name", "value", "type", "len", "typeid"}
  • "value" return an array with all values

Here’s an example for a PLRuby function using a prepared plan :

   CREATE FUNCTION t1_count(int4, int4) RETURNS int4 AS '
       if ! $Plans.key?("plan")
           # prepare the saved plan on the first call
           $Plans["plan"] = PL::Plan.new("SELECT count(*) AS cnt FROM t1
                                          WHERE num >= $1 AND num <= $2",
                                         ["int4", "int4"]).save
       end
       n = $Plans["plan"].exec([args[0], args[1]], 1)
       n["cnt"]
   ' LANGUAGE 'plruby';

Release a query plan

Save a query plan for later execution. The plan is copied to the toplevel memory context.

[Validate]