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
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
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:
Show quoted text
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:
Show quoted text
You can turn up the verbosity of postgres logger to log all SQL
statements. Look in postgresql.confIn 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-9085Dave 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