Module | PLRuby::PL |
In: |
plruby.rb
|
general module
Call parser/planner/optimizer/executor for query. The optional count value tells spi_exec the maximum number of rows to be processed by the query.
If the query is a SELECT statement, an array is return (if count is not specified or with a value > 1). Each element of this array is an hash where the key is the column name.
If type is specified it can take the value
["name", "value", "type", "len", "typeid"]
{"name", "value", "type", "len", "typeid"}
For example this procedure display all rows in the table pg_table.
CREATE FUNCTION pg_table_dis() RETURNS int4 AS ' res = PL.exec("select * from pg_class") res.each do |x| warn "======================" x.each do |y, z| warn "name = #{y} -- value = #{z}" end warn "======================" end return res.size ' LANGUAGE 'plruby';
A block can be specified, in this case a call to yield() will be made.
If count is specified with the value 1, only the first row (or FALSE if it fail) is returned as a hash. Here a little example :
CREATE FUNCTION pg_table_dis() RETURNS int4 AS ' PL.exec("select * from pg_class", 1) { |y, z| warn "name = #{y} -- value = #{z}" } return 1 ' LANGUAGE 'plruby';
Another example with count = 1
create table T_pkey1 ( skey1 int4, skey2 varchar(20), stxt varchar(40) ); create function toto() returns bool as ' warn("=======") PL.exec("select * from T_pkey1", 1, "hash") do |a| warn(a.inspect) end warn("=======") PL.exec("select * from T_pkey1", 1, "array") do |a| warn(a.inspect) end warn("=======") PL.exec("select * from T_pkey1", 1) do |a| warn(a.inspect) end warn("=======") return true ' language 'plruby'; plruby_test=# select toto(); NOTICE: ======= NOTICE: {"name"=>"skey1", "typeid"=>23, "type"=>"int4", "value"=>"12", "len"=>4} NOTICE: {"name"=>"skey2", "typeid"=>1043, "type"=>"varchar", "value"=>"a", "len"=>20} NOTICE: {"name"=>"stxt", "typeid"=>1043, "type"=>"varchar", "value"=>"b", "len"=>40} NOTICE: ======= NOTICE: ["skey1", "12", "int4", 4, 23] NOTICE: ["skey2", "a", "varchar", 20, 1043] NOTICE: ["stxt", "b", "varchar", 40, 1043] NOTICE: ======= NOTICE: ["skey1", "12"] NOTICE: ["skey2", "a"] NOTICE: ["stxt", "b"] NOTICE: ======= toto ------ t (1 row) plruby_test=#
return the number of rows insered, updated, deleted, …
return TRUE
Deprecated : See PL::Plan::new and PL::Plan#save
Prepares AND SAVES a query plan for later execution. It is a bit different from the C level SPI_prepare in that the plan is automatically copied to the toplevel memory context.
If the query references arguments, the type names must be given as a Ruby array of strings. The return value from prepare is a PL::Plan object to be used in subsequent calls to PL::Plan#exec.
If the hash given has the keys count, output these values will be given to the subsequent calls to each
Duplicates all occurences of single quote and backslash characters. It should be used when variables are used in the query string given to spi_exec or spi_prepare (not for the value list on execp).