Thread: Using PL/R for predictive analysis of data.

Hi Everyone,

I am wanting to ask some opinions on implementing PL/R into V8.3 on
Win32. I have a need to be able to perform some relatively demanding
statistical functions as the basis of producing data for reports.

In short R appears to have more than enough capability to do the job
(from a statistical perspective), however there doesnt seem to be that
much discussion on using the PL/R implementation, or for that matter
tutorials on using PL/R.

What I would like to know is:
1/ Is is possible to create a view that has its columns based on the
output of a PL/R function?

2/ Are there special considerations for the source data?

3/ Has anyone any experience with NonLinear Regression Analysis using
PL/R to predict future outcomes as a resultset? (ie/ the equivalent of
output from an SQL SELECT statement)

Any advice or counsel would be greatly appreciated.

I am not super famailar with Postgres, but I am famailar with other
large scale databases as well as some desktop ones so dont be afraid
to throw technical answers if you need to :-)

Greatly appreciated

The Frog

<BUMP>

On Wed, Mar 12, 2008 at 09:35:24AM -0700, Mr.Frog.to.you@googlemail.com wrote:
> In short R appears to have more than enough capability to do the job
> (from a statistical perspective), however there doesnt seem to be that
> much discussion on using the PL/R implementation, or for that matter
> tutorials on using PL/R.

I've not used PL/R either, but I've used R on it's own a bit and I've
done a bit of PL/pgSQL programming as well.

> What I would like to know is:
> 1/ Is is possible to create a view that has its columns based on the
> output of a PL/R function?

You're after something called set returning functions, i.e. a function
that has "RETURNS SETOF" in it's definition.  There's an example in the
docs[1].  To create a view from this example, do

  CREATE VIEW emps AS
    SELECT * FROM get_emps();

The problem with this is that the optimiser can't "see inside" the
function and do much fancy optimisation any more.  I can't help with
your other questions.

> Any advice or counsel would be greatly appreciated.

I'd get everything working separately before trying to bring it all
together.  I.e. get the regressions working in plain R using data
extracted by hand before trying to do everything in PG.


  Sam

I am using a 3rd front end to generate  postgres requests , its reportng
an error with the database.

Is there anyway of loging which sql requests the application is  actual
sending to postgres.  I need to known if the error is being created by
the application generating invalid SQL or if there is a problem with the
desgin of the database tables.

Dave.

On Saturday 15 March 2008 2:29 pm, Dave Potts wrote:
> I am using a 3rd front end to generate  postgres requests , its reportng
> an error with the database.
>
> Is there anyway of loging which sql requests the application is  actual
> sending to postgres.  I need to known if the error is being created by
> the application generating invalid SQL or if there is a problem with the
> desgin of the database tables.
>
> Dave.
See:
http://www.postgresql.org/docs/8.2/interactive/runtime-config-logging.html
In particular you will want to set log_statement to 'all'  to see the SQL.
--
Adrian Klaver
aklaver@comcast.net

Terry Fielder
terry@greatgulfhomes.com
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085

I am using a 3rd front end to generate  postgres requests , its reportng an error with the database.

Is there anyway of loging which sql requests the application is  actual sending to postgres.  I need to known if the error is being created by the application generating invalid SQL or if there is a problem with the desgin of the database tables.

Dave.

Thanks everbody  I have isolated the issue


Davel,



Terry Fielder wrote:
> You can turn up the verbosity of postgres logger to log all SQL
> statements.  Look in postgresql.conf
>
> In particular, you can set postgres to log statements that take over x
> milliseconds to execute.  If you set log_min_duration_statement to 0,
> then it will log ALL statements, which could also give you what you
> want if you want to see all SQL statements.
>
> Terry
>
> Terry Fielder
> terry@greatgulfhomes.com
> Associate Director Software Development and Deployment
> Great Gulf Homes / Ashton Woods Homes
> Fax: (416) 441-9085
>
>
> Dave Potts wrote:
>>
>>
>> I am using a 3rd front end to generate  postgres requests , its
>> reportng an error with the database.
>>
>> Is there anyway of loging which sql requests the application is
>> actual sending to postgres.  I need to known if the error is being
>> created by the application generating invalid SQL or if there is a
>> problem with the desgin of the database tables.
>>
>> Dave.
>> ------------------------------------------------------------------------
>>
>>
>>

Hi Sam,

Thankyou for the suggestions. They make perfect sense to me. I
appreciate your time and input. The lack of optimiser usage was
something that I had not considered, and I thank you for making me
aware of it.

Cheers

The Frog

On Mon, Mar 17, 2008 at 2:27 AM, Mr.Frog.to.you@googlemail.com
<Mr.Frog.to.you@googlemail.com> wrote:
> Hi Sam,
>
>  Thankyou for the suggestions. They make perfect sense to me. I
>  appreciate your time and input. The lack of optimiser usage was
>  something that I had not considered, and I thank you for making me
>  aware of it.
>
>  Cheers
>
>  The Frog

On the subject of the planner and optimizer, as of 8.3 (I think it's
new to 8.3...) you can tell the planner somewhat about how it might
expect your function to behave. See
http://www.postgresql.org/docs/8.3/interactive/sql-createfunction.html

- Josh/eggyknap