Re: pl/R questions

From: Mike Mascari <mascarm(at)mascari(dot)com>
To: Joe Conway <mail(at)joeconway(dot)com>
Cc: postgresql <pgsql-general(at)postgresql(dot)org>
Subject: Re: pl/R questions
Date: 2003-08-02 18:06:45
Message-ID: 3F2BFDB5.2090104@mascari.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Joe Conway wrote:

> Mike Mascari wrote:
>
>> (A) The function r_resetlm() must be called to reset the global values
>> before each invocation. Not a big problem, but I would like to avoid
>> globals, if possible. The relations supplying the data are temporary
>> tables and thus I cannot refer to their names in static pl/R. I can't
>> figure out a way to use pg.spi.prepare()/pg.spi.execp() to initialize
>> R variables with the result of the executed queries. I would like to
>> do something like this, instead:
>
> I don't think you can do a prepared plan if the table itself is going to
> change, only when parameters change. Maybe something like this works:
>
> CREATE OR REPLACE FUNCTION r_predict(text, text)
> RETURNS SETOF RECORD AS '
> sql <- paste("SELECT x, y FROM", arg1, "ORDER BY x")
> xyknowns <- pg.spi.exec(sql)
> xs <- as.numeric(xyknowns[,1])
> ys <- as.numeric(xyknowns[,2])
>
> sql <- paste("SELECT x FROM", arg2, "ORDER BY x")
> xypred <- pg.spi.exec(sql)
> nxs <- as.numeric(xypred[,1])
>
> samples <- data.frame(xs=nxs)
> result <- predict(lm(ys ~ xs), samples)
> return (result)
> ' LANGUAGE 'plr' WITH (isStrict);
>
> regression=# select * from r_predict('entries', 'predictions') as
> trend(ny float8);
> ny
> ------------------
> 146171.515151515
> 147189.696969697
> 148207.878787879
> 149226.060606061
> 150244.242424242
> (5 rows)

Very nice. Thanks a bunch!

> I'm sure there is support for multivariate linear regression in R, but
> I'm still too new at R to know the answer myself. You should try posting
> that one to R-help.

Okay.

> BTW, I created a PL/R specific mailing list on gborg, but no one is
> subscribed currently. If people on this list find PL/R specific
> questions too off-topic, perhaps we should move there. R specific
> questions should definitely be posted to R-help though.

Okay. Great! Thanks again!

Mike Mascari
mascarm(at)mascari(dot)com

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Francisco J Reyes 2003-08-02 18:22:39 Inheritance vs child tables (Was Domains)
Previous Message Joe Conway 2003-08-02 17:13:40 Re: pl/R questions