CODEWITHSUNDEEP
perlrpostgresqlplr
punkish
punkish

Reputation: 15268

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

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.

Upvotes: 1

Views: 266

Answers (1)

Chris Travers
Chris Travers

Reputation: 26464

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.

Upvotes: 0

Related Questions