Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Browse files

Fix several more locations where NULL and NA semantics were wrong or

at least inconsistent with other recent changes.

Update docs for NULL/NA semantic changes and wrote new section for triggers.
  • Loading branch information...
commit 706de734e94339c6709e8ebabc7bde04ae38c89b 1 parent 344bdea
postgres authored
Showing with 230 additions and 62 deletions.
  1. +4 −1 README.plr
  2. +179 −24 doc/plr.sgml
  3. +47 −37 pg_conversion.c
5 README.plr
View
@@ -37,12 +37,15 @@ Version 0.2 (15 February, 2003):
docs. Too many to mention, but faithfully recorded in cvs commit
messages.
+Version 0.5 (5 August, 2003):
+ - See http://www.joeconway.com/plr/ for release notes
+
Installation:
Place these files in a directory called 'plr' under 'contrib' in the
PostgreSQL source tree. E.g. place the tarred source file in
'/path/to/pg_source/contrib' and run:
- tar -xzf plr.x.y.z.tar.gz
+ tar -xzf plr-x.y.z-alpha.tar.gz
Then run:
203 doc/plr.sgml
View
@@ -13,9 +13,10 @@
<para>
PL/R is a loadable procedural language that enables you to write
- PostgreSQL functions in the <ulink url="http://www.r-project.org/">R
- programming language</ulink>. PL/R offers most (if not all) of the
- capabilities a function writer has in the R language.
+ PostgreSQL functions and triggers in the
+ <ulink url="http://www.r-project.org/">R programming language</ulink>.
+ PL/R offers most (if not all) of the capabilities a function writer has
+ in the R language.
</para>
<para>
@@ -144,10 +145,10 @@ CREATE OR REPLACE FUNCTION r_max (integer, integer) RETURNS integer AS '
return(arg1)
else
return(arg2)
-' LANGUAGE 'plr' WITH (isStrict);
+' LANGUAGE 'plr' STRICT;
</programlisting>
- Note the clause <literal>WITH (isStrict)</>, which saves us from
+ Note the clause <literal>STRICT</>, which saves us from
having to think about NULL input values: if a NULL is passed, the
function will not be called at all, but will just return a NULL
result automatically.
@@ -156,17 +157,17 @@ CREATE OR REPLACE FUNCTION r_max (integer, integer) RETURNS integer AS '
<para>
In a non-strict function, if the actual value of an argument
is NULL, the corresponding <literal>argN</literal> variable will be set
- to an <literal>NA</literal> string. For example, suppose that we wanted
+ to a <literal>NULL</literal> R object. For example, suppose that we wanted
<function>r_max</function> with one null and one non-null argument to
return the non-null argument, rather than NULL:
<programlisting>
CREATE OR REPLACE FUNCTION r_max (integer, integer) RETURNS integer AS '
- if (is.na(arg1) && is.na(arg2))
- return(NA)
- if (is.na(arg1))
+ if (is.null(arg1) && is.null(arg2))
+ return(NULL)
+ if (is.null(arg1))
return(arg2)
- if (is.na(arg2))
+ if (is.null(arg2))
return(arg1)
if (arg1 > arg2)
return(arg1)
@@ -177,7 +178,7 @@ CREATE OR REPLACE FUNCTION r_max (integer, integer) RETURNS integer AS '
<para>
As shown above, to return a NULL value from a PL/R function, return
- <literal>NA</literal>. This can be done whether the function is strict
+ <literal>NULL</literal>. This can be done whether the function is strict
or not.
</para>
@@ -261,16 +262,6 @@ select round(sd('{1.23,1.31,1.42,1.27}'::_float8)::numeric,8);
</para>
</tip>
- <tip>
- <para>
- In PostgreSQL 7.3.x, declaring an array function
- <emphasis>argument</emphasis> using the brackets syntax
- (<type>datatype</type>[]) does not work correctly. Use the underscore
- syntax instead (_<type>datatype</type>). In the next PostgreSQL release,
- this should be fixed, and either syntax will work.
- </para>
- </tip>
-
</chapter>
<chapter id="plr-data">
@@ -640,7 +631,7 @@ select test_spi_prep('select oid, typname from pg_type
OK
(1 row)
-create or replace function test_spi_execp(text) returns record as '
+create or replace function test_spi_execp(text) returns setof record as '
pg.spi.execp(pg.reval(arg1), NA)
' language 'plr';
@@ -663,7 +654,7 @@ select test_spi_prep('select oid, typname from pg_type
OK
(1 row)
-create or replace function test_spi_execp(text) returns record as '
+create or replace function test_spi_execp(text) returns setof record as '
pg.spi.execp(pg.reval(arg1))
' language 'plr';
@@ -1052,8 +1043,172 @@ select pg_test_module_load('hello world');
<chapter id="plr-trigger-func">
<title>Trigger Procedures</title>
+
+ <indexterm>
+ <primary>triggers</primary>
+ <secondary>in PL/R</secondary>
+ </indexterm>
+
+ <para>
+ Trigger procedures can be written in PL/R.
+ <productname>PostgreSQL</productname> requires that a procedure that is to be called
+ as a trigger must be declared as a function with no arguments
+ and a return type of <literal>trigger</>.
+ </para>
+ <para>
+ The information from the trigger manager is passed to the procedure body
+ in the following variables:
+
+ <variablelist>
+
+ <varlistentry>
+ <term><varname>pg.tg.name</varname></term>
+ <listitem>
+ <para>
+ The name of the trigger from the <command>CREATE TRIGGER</command> statement.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><varname>pg.tg.relid</varname></term>
+ <listitem>
+ <para>
+ The object ID of the table that caused the trigger procedure
+ to be invoked.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><varname>pg.tg.relname</varname></term>
+ <listitem>
+ <para>
+ The name of the table that caused the trigger procedure
+ to be invoked.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><varname>pg.tg.when</varname></term>
+ <listitem>
+ <para>
+ The string <literal>BEFORE</> or <literal>AFTER</> depending on the
+ type of trigger call.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><varname>pg.tg.level</varname></term>
+ <listitem>
+ <para>
+ The string <literal>ROW</> or <literal>STATEMENT</> depending on the
+ type of trigger call.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><varname>pg.tg.op</varname></term>
+ <listitem>
+ <para>
+ The string <literal>INSERT</>, <literal>UPDATE</>, or
+ <literal>DELETE</> depending on the type of trigger call.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><varname>pg.tg.new</varname></term>
+ <listitem>
+ <para>
+ When the trigger is defined <command>FOR EACH ROW</>,
+ a data.frame containing the values of the new table
+ row for <command>INSERT</> or <command>UPDATE</> actions. For
+ triggers defined <command>FOR EACH STATEMENT</> and for
+ <command>DELETE</> actions, set to <literal>NULL</>. The
+ atribute names are the table's column names. Columns that are
+ null will be represented as NA.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><varname>pg.tg.new</varname></term>
+ <listitem>
+ <para>
+ When the trigger is defined <command>FOR EACH ROW</>,
+ a data.frame containing the values of the old table
+ row for <command>DELETE</> or <command>UPDATE</> actions. For
+ triggers defined <command>FOR EACH STATEMENT</> and for
+ <command>INSERT</> actions, set to <literal>NULL</>. The
+ atribute names are the table's column names. Columns that are
+ null will be represented as NA.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><varname>pg.tg.args</varname></term>
+ <listitem>
+ <para>
+ A vector of the arguments to the procedure as given in the
+ <command>CREATE TRIGGER</command> statement.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </para>
+
<para>
- Currently trigger procedures are not supported by PL/R.
+ The return value from a trigger procedure can be <literal>NULL</> or a
+ one row data.frame matching the number and type of columns in the trigger
+ table. <literal>NULL</> tells the trigger manager to silently
+ suppress the operation for this row. If a one row data.frame is returned,
+ it tells PL/R to return a possibly modified row to the trigger manager
+ that will be inserted instead of the one given in <varname>pg.tg.new</>.
+ This works for <command>INSERT</> and <command>UPDATE</> only. Needless
+ to say that all this is only meaningful when the trigger is
+ <literal>BEFORE</> and <command>FOR EACH ROW</>; otherwise the return
+ value is ignored.
+ </para>
+ <para>
+ Here's a little example trigger procedure that forces an integer value
+ in a table to keep track of the number of updates that are performed on the
+ row. For new rows inserted, the value is initialized to 0 and then
+ incremented on every update operation.
+
+<programlisting>
+CREATE FUNCTION trigfunc_modcount() RETURNS trigger AS '
+
+ if (pg.tg.op == "INSERT")
+ {
+ retval <- pg.tg.new
+ retval[pg.tg.args[1]] <- 0
+ }
+ if (pg.tg.op == "UPDATE")
+ {
+ retval <- pg.tg.new
+ retval[pg.tg.args[1]] <- pg.tg.old[pg.tg.args[1]] + 1
+ }
+ if (pg.tg.op == "DELETE")
+ retval <- pg.tg.old
+
+ return(retval)
+' LANGUAGE plr;
+
+CREATE TABLE mytab (num integer, description text, modcnt integer);
+
+CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab
+ FOR EACH ROW EXECUTE PROCEDURE trigfunc_modcount('modcnt');
+</programlisting>
+
+ Notice that the trigger procedure itself does not know the column
+ name; that's supplied from the trigger arguments. This lets the
+ trigger procedure be reused with different tables.
</para>
</chapter>
84 pg_conversion.c
View
@@ -81,19 +81,11 @@ pg_scalar_get_r(Datum dvalue, Oid arg_typid, FmgrInfo arg_out_func)
(Datum) 0,
Int32GetDatum(-1)));
- if (value != NULL)
- {
- /* get new vector of the appropriate type, length 1 */
- PROTECT(result = get_r_vector(arg_typid, 1));
+ /* get new vector of the appropriate type, length 1 */
+ PROTECT(result = get_r_vector(arg_typid, 1));
- /* add our value to it */
- pg_get_one_r(value, arg_typid, &result, 0);
- }
- else
- {
- PROTECT(result = NEW_CHARACTER(1));
- SET_STRING_ELT(result, 0, NA_STRING);
- }
+ /* add our value to it */
+ pg_get_one_r(value, arg_typid, &result, 0);
UNPROTECT(1);
@@ -102,7 +94,7 @@ pg_scalar_get_r(Datum dvalue, Oid arg_typid, FmgrInfo arg_out_func)
/*
- * given an array pg value, convert to a multi-row R vector
+ * Given an array pg value, convert to a multi-row R vector.
*/
SEXP
pg_array_get_r(Datum dvalue, FmgrInfo out_func, int typlen, bool typbyval, char typalign)
@@ -124,6 +116,10 @@ pg_array_get_r(Datum dvalue, FmgrInfo out_func, int typlen, bool typbyval, char
*dim;
char *p;
+ /* short-circuit for NULL datums */
+ if (dvalue == (Datum) NULL)
+ return R_NilValue;
+
ndim = ARR_NDIM(v);
element_type = ARR_ELEMTYPE(v);
dim = ARR_DIMS(v);
@@ -178,10 +174,14 @@ pg_array_get_r(Datum dvalue, FmgrInfo out_func, int typlen, bool typbyval, char
p = att_addlength(p, typlen, PointerGetDatum(p));
p = (char *) att_align(p, typalign);
- if (value != NULL)
- pg_get_one_r(value, element_type, &result, idx);
- else
- SET_STRING_ELT(result, idx, NA_STRING);
+ /*
+ * Note that pg_get_one_r() replaces NULL values with
+ * the NA value appropriate for the data type. Not presently
+ * a concern anyway, but when Postgres arrays start allowing
+ * NULL elements, nothing needs to change here. That's a
+ * Good Thing.
+ */
+ pg_get_one_r(value, element_type, &result, idx);
}
}
}
@@ -222,7 +222,7 @@ pg_tuple_get_r_frame(int ntuples, HeapTuple *tuples, TupleDesc tupdesc)
SEXP fldvec;
if (tuples == NULL || ntuples < 1)
- return(R_NilValue);
+ return R_NilValue;
/*
* Allocate the data.frame initially as a list,
@@ -278,10 +278,7 @@ pg_tuple_get_r_frame(int ntuples, HeapTuple *tuples, TupleDesc tupdesc)
char *value;
value = SPI_getvalue(tuples[i], tupdesc, j + 1);
- if (value != NULL)
- pg_get_one_r(value, element_type, &fldvec, i);
- else
- SET_STRING_ELT(fldvec, i, NA_STRING);
+ pg_get_one_r(value, element_type, &fldvec, i);
}
else
{
@@ -291,13 +288,7 @@ pg_tuple_get_r_frame(int ntuples, HeapTuple *tuples, TupleDesc tupdesc)
SEXP fldvec_elem;
dvalue = SPI_getbinval(tuples[i], tupdesc, j + 1, &isnull);
- if (!isnull)
- PROTECT(fldvec_elem = pg_array_get_r(dvalue, outputproc, typlen, typbyval, typalign));
- else
- {
- PROTECT(fldvec_elem = NEW_CHARACTER(1));
- SET_STRING_ELT(fldvec_elem, 0, NA_STRING);
- }
+ PROTECT(fldvec_elem = pg_array_get_r(dvalue, outputproc, typlen, typbyval, typalign));
SET_VECTOR_ELT(fldvec, i, fldvec_elem);
UNPROTECT(1);
@@ -379,7 +370,10 @@ pg_get_one_r(char *value, Oid typtype, SEXP *obj, int elnum)
case INT2OID:
case INT4OID:
/* 2 and 4 byte integer pgsql datatype => use R INTEGER */
- INTEGER_DATA(*obj)[elnum] = atoi(value);
+ if (value)
+ INTEGER_DATA(*obj)[elnum] = atoi(value);
+ else
+ INTEGER_DATA(*obj)[elnum] = NA_INTEGER;
break;
case INT8OID:
case FLOAT4OID:
@@ -391,14 +385,23 @@ pg_get_one_r(char *value, Oid typtype, SEXP *obj, int elnum)
* Note pgsql int8 is mapped to R REAL
* because R INTEGER is only 4 byte
*/
- NUMERIC_DATA(*obj)[elnum] = atof(value);
+ if (value)
+ NUMERIC_DATA(*obj)[elnum] = atof(value);
+ else
+ NUMERIC_DATA(*obj)[elnum] = NA_REAL;
break;
case BOOLOID:
- LOGICAL_DATA(*obj)[elnum] = ((*value == 't') ? 1 : 0);
+ if (value)
+ LOGICAL_DATA(*obj)[elnum] = ((*value == 't') ? 1 : 0);
+ else
+ LOGICAL_DATA(*obj)[elnum] = NA_LOGICAL;
break;
default:
/* Everything else is defaulted to string */
- SET_STRING_ELT(*obj, elnum, COPY_TO_USER_STRING(value));
+ if (value)
+ SET_STRING_ELT(*obj, elnum, COPY_TO_USER_STRING(value));
+ else
+ SET_STRING_ELT(*obj, elnum, NA_STRING);
}
}
@@ -1031,7 +1034,6 @@ get_frame_tuplestore(SEXP rval,
SET_VECTOR_ELT(result, j, dfcol);
}
-
UNPROTECT(1);
}
@@ -1063,7 +1065,7 @@ get_frame_tuplestore(SEXP rval,
}
else
{
- if (STRING_ELT(dfcol, 0) != NA_STRING)
+ if (STRING_ELT(dfcol, i) != NA_STRING)
values[j] = pstrdup(CHAR(STRING_ELT(dfcol, i)));
else
values[j] = NULL;
@@ -1135,7 +1137,12 @@ get_matrix_tuplestore(SEXP rval,
for(i = 0; i < nr; i++)
{
for (j = 0; j < nc; j++)
- values[j] = CHAR(STRING_ELT(obj, (j * nr) + i));
+ {
+ if (STRING_ELT(obj, (j * nr) + i) != NA_STRING)
+ values[j] = CHAR(STRING_ELT(obj, (j * nr) + i));
+ else
+ values[j] = NULL;
+ }
/* construct the tuple */
tuple = BuildTupleFromCStrings(attinmeta, values);
@@ -1197,7 +1204,10 @@ get_generic_tuplestore(SEXP rval,
for(i = 0; i < nr; i++)
{
- values[0] = CHAR(STRING_ELT(obj, i));
+ if (STRING_ELT(obj, i) != NA_STRING)
+ values[0] = CHAR(STRING_ELT(obj, i));
+ else
+ values[0] = NULL;
/* construct the tuple */
tuple = BuildTupleFromCStrings(attinmeta, values);
Please sign in to comment.
Something went wrong with that request. Please try again.