[R] Storing data frame in a RDBMS

[Joe Conway]

Gabor Grothendieck wrote:
> On 6/4/05, Adam Witney <awitney at sgul.ac.uk> wrote:
>>I am using PL/R in PostgreSQL amd have written some functions to build my
>>data frame. However this can take some time with some large datasets and I
>>would like to not have to repeat the process and so I would like to save the
>>data frame. Rather than save/load into the file system I would like to be
>>able to save the entire data frame as a single object in the database
>>
>>Is this possible?
> 
> Check out ?serialize

Looks like serialize should work nicely:

create or replace function test_serialize(text)
  returns text as '
   mydf <- pg.spi.exec(arg1)
   return (serialize(mydf, NULL, ascii = TRUE))
' language 'plr';

create table saved_df (id int, df text);

insert into saved_df
  select 1, f from test_serialize('select oid, typname from pg_type
                                   where typname = ''oid''
                                   or typname = ''text''') as t(f);

create or replace function restore_df(text)
  returns setof record as '
   unserialize(arg1)
' language 'plr';

select * from restore_df((select df from saved_df where id =1))
  as t(oid oid, typname name);
  oid | typname
-----+---------
   25 | text
   26 | oid
(2 rows)

HTH,

Joe