returning multiple dissimilar data structures from R function in PL/R

Question

I have been looking at various discussions here on SO and other places, and the general consensus seems that if one is returning multiple non-similar data structures from an R function, they are best returned as a list(a, b) and then accessed by the indexes 0 and 1 and so on. Except, when using an R function via PL/R inside a Perl program, the R list function flattens the list, and also stringifies even the numbers. For example

my $res = $sth->fetchrow_arrayref;
# now, $res is a single, flattened, stringified list
# even though the R function was supposed to return 
# list([1, "foo", 3], [2, "bar"])
#
# instead, $res looks like c(\"1\", \""foo"\", \"3\", \"2\", \""bar"\")
# or some such nonsense

Using a data.frame doesn't work because the two arrays being returned are not symmetrical, and the function croaks.

So, how do I return a single data structure from an R function that is made up of an arbitrary set of nested data structures, and still be able to access each individual bundle from Perl as simply $res->[0], $res->[1] or $res->{'employees'}, $res->{'pets'}? update: I am looking for an R equiv of Perl's [[1, "foo", 3], [2, "bar"]] or even [[1, "foo", 3], {a => 2, b => "bar"}]

addendum: The main thrust of my question is how to return multiple dissimilar data structures from a PL/R function. However, the stringification, as noted above, and secondary, is also problematic because I convert the data to JSON, and all those extra quotes just add to useless data transferred between the server and the user.

Since perl is weakly typed it won't matter if the numbers are stringified. — Ilion, Jan 14 '12 at 14:55
Added clarifying addendum to question. Briefly, while stringification is a secondary problem, it is indeed an issue. One, I can't get the separate data structures out using indexes as I would with an array or hash, and two, all the extra quotes just gum up the works, adding fat to my data transfer. — punkish, Jan 14 '12 at 15:28

score 0 · Answer 1 · answered Mar 25 '13 at 01:30

I think you have a few problems here. The first is you can't just return an array in this case because it won't pass PostgreSQL's array checks (arrays must be symmetric, all of the same type, etc). Remember that if you are calling PL/R from PL/Perl across a query interface, PostgreSQL type constraints are going to be an issue.

You have a couple of options.

You could return setof text[], with one data type per row.

you could return some sort of structured data using structures PostgreSQL understands, like:

CREATE TYPE ab AS (
   a text,
   b text
);

CREATE TYPE r_retval AS (
   labels text[],
   my_ab ab
);

This would allow you to return something like:

{labels => [1, "foo", 3], ab => {a => 'foo', b => 'bar'} }

But at any rate you have to put it into a data structure that the PostgreSQL planner can understand and that is what I think is missing in your example.

returning multiple dissimilar data structures from R function in PL/R

1 Answers1