Chapter 4. Passing Data Values

The argument values supplied to a PL/R function's script are the input arguments converted to a corresponding R form. See Table 4-1. Scalar PostgreSQL values become single element R vectors. One exception to this are scalar bytea values. These are first converted to R raw type, and then processed by the R unserialize command. One-dimensional PostgreSQL arrays are converted to multi-element R vectors, two-dimensional PostgreSQL arrays are mapped to R matrixes, and three-dimensional PostgreSQL arrays are converted to three-dimensional R arrays. Greater than three-dimensional arrays are not supported. Composite-types are transformed into R data.frames.

Table 4-1. Function Arguments

PostgreSQL typeR type
booleanlogical
int2, int4integer
int8, float4, float8, cash, numericnumeric
byteaobject
everything elsecharacter

Conversely, the return values are first coerced to R character, and therefore anything that resolves to a string that is acceptable input format for the function's declared return type will produce a result. Again, there is an exception for scalar bytea return values. In this case, the R object being returned is first processed by the R serialize command, and then the binary result is directly mapped into a PostgreSQL bytea datum. Similar to argument conversion, there is also a mapping between the dimensionality of the declared PostgreSQL return type and the type of R object. That mapping is shown in Table 4-2

Table 4-2. Function Result Dimensionality

PgSQL return typeR typeResultExample
scalararray, matrix, vectorfirst column of first rowc(1,2,3) in R returns 1 in PostgreSQL
setof scalar1D array, greater than 2D array, vectormulti-row, 1 column setarray(1:10) in R returns 10 rows in PostgreSQL
scalardata.frametextual representation of the first column's vectordata.frame(c(1,2,3)) in R returns 'c(1, 2, 3)'
setof scalar2D array, matrix, data.frame#columns > 1, error; #columns == 1, multi-row, 1 column set(as.data.frame(array(1:10,c(2,5))))[,1] in R returns 2 rows of scalar
array1D array, greater than 3D array, vector1D arrayarray(1:8,c(2,2,2,2)) in R returns {1,2,3,4,5,6,7,8,1,2,3,4,5,6,7,8}
array2D array, matrix, data.frame2D arrayarray(1:4,c(2,2)) in R returns {{1,3},{2,4}}
array3D array3D arrayarray(1:8,c(2,2,2)) in R returns {{{1,5},{3,7}},{{2,6},{4,8}}}
composite1D array, greater than 2D array, vectorfirst row, 1 columnarray(1:8,c(2,2,2)) in R returns 1 row of scalar
setof composite1D array, greater than 2D array, vectormulti-row, 1 column setarray(1:8,c(2,2,2)) in R returns 8 rows of scalar
composite2D array, matrix, data.framefirst row, multi-columnarray(1:4,c(2,2)) in R returns 1 row of 2 columns
setof composite2D array, matrix, data.framemulti-row, multi-column setarray(1:4,c(2,2)) in R returns 2 rows of 2 columns