Thread: Is this possible or am I on drugs :)
I have a web application that needs to generate a report. Said report requires a lot of calcuations so that hard coding them is a pain. I would like to set up a view does does all the apropriate selects and calculations to make writing my report easier. Is it possible to pass a user supplied parameter to a view? Would a procedure make this easier? I could write the query in the code to accomplish want I want but I'm always looking for new and interesting ways to accomplish things. :) From a general point of view I want to do something like: select stuff from table where something=UserSuppliedParameter. Is this possible to do from within Postgres? How would I go about getting the User Supplied Parameter? How would I pass it using PHP? Any comments would be apreciated Shawn
If I'm reading correctly your basically just asking "how do i do php ?" I'd reccommend any one of the many tutorials on the net, but i'll include a gist.. create an HTML form with a text input form. name it "input" in your php script connect to the database and do $query = pg_exec($dbconnection,"SELECT * from mytable WHERE badabing='$input'"); that's the gist.. jeff On Mon, 27 Nov 2000, shawn everett wrote: > I have a web application that needs to generate a report. Said report > requires a lot of calcuations so that hard coding them is a pain. > > I would like to set up a view does does all the apropriate selects and > calculations to make writing my report easier. Is it possible to pass a > user supplied parameter to a view? Would a procedure make this easier? > > I could write the query in the code to accomplish want I want but I'm > always looking for new and interesting ways to accomplish things. :) > > >From a general point of view I want to do something like: > > select stuff from table where something=UserSuppliedParameter. > > Is this possible to do from within Postgres? How would I go about getting > the User Supplied Parameter? How would I pass it using PHP? > > Any comments would be apreciated > > Shawn > Jeff MacDonald, ----------------------------------------------------- PostgreSQL Inc | Hub.Org Networking Services jeff@pgsql.com | jeff@hub.org www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 ----------------------------------------------------- Facsimile : 1 902 542 5386 IRC Nick : bignose
That was so completely misread as to not even be accurate :) Thanks for what really is a good answer though. :) My origonal posting wasn't too clear now that I look back... :) Let see if I can rephrase things a bit... I am trying to create a report that puts out ~40 different fields. Some of these fields are direct values from a database table while others are calculations on those fields. The calculations are relatively simple, there's just a lot of them and they're not grouped in a convienient way. What I'd like to figure out is the best way to handle the say ~30 calculations I'll need to do to generate the report. I could query the database and then do the calcuations in code via php, I could do them using one giant select statement, or I could possibly dump them all into a view provided I could find a way to pass a parameter to the view. This passing a parameter to a view using PHP was what I was trying to get at in my last post. My initial preference was to let the DBMS handle the calculations. What would be the most apropriate way? Any thoughts you have would be apreciated. :) Shawn
shawn everett <everett@pgweb.com> writes:
> What I'd like to figure out is the best way to handle the say ~30
> calculations I'll need to do to generate the report. I could query the
> database and then do the calcuations in code via php, I could do them
> using one giant select statement, or I could possibly dump them all into a
> view provided I could find a way to pass a parameter to the view.
What do you consider "passing a parameter to the view" to be?
A view is a table --- one computed on-demand, rather than stored, but
still a table. You can use a SELECT ... WHERE to select a subset of
the rows in the table, and (with any luck ;-)) only those rows will
get computed.
If you can express your parameterization in terms of selecting some
rows and not others from a (possibly very large) virtual table, you're
all set.
There will probably be support in 7.2 or so for functions returning
recordsets as SELECT sources. A construct like
SELECT * FROM myfunc(42, 'foo');
would be a truly parameterized view, I think. But we can't do it today.
regards, tom lane
> What do you consider "passing a parameter to the view" to be? > This is where Microsoft Access has twisted me :) Access as you may or may not know allows you to use parameters in a query: SELECT * FROM table WHERE table.pkey=[Enter The Primary Key]; The bit in [] represents a prompt to the user. They can also be filled in programatically. The basic select statement for my problem is going to work as follows: select colA, colB, colA+colB*0.4 as f1, colC, colC+colD*9 as f2 from table where date=SomeDateEnteredByTheUser; Can I pass SomeDateEnteredByTheUser to Postgres in some way and get back the set of records I want? If I can is there a way to call this from PHP? If I can't and end up dynamically writing the query and sumbitting that to Postgres is this a smart way to do it? Or should I simply query out the basic values: colA, colB, colC and colD and then do the calculations within PHP? Shawn
On Mon, 27 Nov 2000, shawn everett wrote: > select colA, colB, colA+colB*0.4 as f1, colC, colC+colD*9 as f2 from table > where date=SomeDateEnteredByTheUser; CREATE VIEW v1 AS select colA, colB, colA+colB*0.4 as f1, colC, colC+colD*9 as f2, date from table; SELECT colA, colB, f1, colC, f2 FROM v1 where date=SomeDateEnteredByTheUser; -- Tod McQuillin
shawn everett <everett@pgweb.com> writes:
> The basic select statement for my problem is going to work as follows:
> select colA, colB, colA+colB*0.4 as f1, colC, colC+colD*9 as f2 from table
> where date=SomeDateEnteredByTheUser;
OK, no problem:
create view myview as
select date, colA, colB, colA+colB*0.4 as f1, colC, colC+colD*9 as f2
from table;
Then your app does
select * from myview where date = SomeDateEnteredByTheUser;
(if you don't want to include the date in the display, you can't
just write * for the output columns, but hopefully you get the
idea now...)
regards, tom lane
Jeff MacDonald wrote: > If I'm reading correctly your basically just asking "how do i do php ?" > > I'd reccommend any one of the many tutorials on the net, but i'll include a gist.. > > create an HTML form with a text input form. name it "input" > > in your php script connect to the database and do > > $query = pg_exec($dbconnection,"SELECT * from mytable WHERE badabing='$input'"); > > that's the gist.. > > jeff > > On Mon, 27 Nov 2000, shawn everett wrote: > >> I have a web application that needs to generate a report. Said report >> requires a lot of calcuations so that hard coding them is a pain. >> >> I would like to set up a view does does all the apropriate selects and >> calculations to make writing my report easier. Is it possible to pass a >> user supplied parameter to a view? Would a procedure make this easier? >> >> I could write the query in the code to accomplish want I want but I'm >> always looking for new and interesting ways to accomplish things. :) >> >> >From a general point of view I want to do something like: >> >> select stuff from table where something=UserSuppliedParameter. >> >> Is this possible to do from within Postgres? How would I go about getting >> the User Supplied Parameter? How would I pass it using PHP? >> >> Any comments would be apreciated >> >> Shawn >> > > Jeff MacDonald, > > ----------------------------------------------------- > PostgreSQL Inc | Hub.Org Networking Services > jeff@pgsql.com | jeff@hub.org > www.pgsql.com | www.hub.org > 1-902-542-0713 | 1-902-542-3657 > ----------------------------------------------------- > Facsimile : 1 902 542 5386 > IRC Nick : bignose > > Try phpDBlib.
shawn everett wrote: > > What do you consider "passing a parameter to the view" to be? > This is where Microsoft Access has twisted me :) > Access as you may or may not know allows you to use parameters in a query: > SELECT * FROM table WHERE table.pkey=[Enter The Primary Key]; > The bit in [] represents a prompt to the user. They can also be filled in > programatically. In PHP, that's just a variable passed from a prior page.... > The basic select statement for my problem is going to work as follows: > select colA, colB, colA+colB*0.4 as f1, colC, colC+colD*9 as f2 from table > where date=SomeDateEnteredByTheUser; > Can I pass SomeDateEnteredByTheUser to Postgres in some way and get back > the set of records I want? If I can is there a way to call this from PHP? You _are_ asking pretty much a basic PHP question. $result = pg_exec($conn,"select colA, colB, colA+colB*0.4 as f1, colC, colC+colD*9 as f2 from table where date=$SomeDateEnteredByTheUser;"); Where you are passing "$SomeDateEnteredByTheUser" from a prior page as a form item. Want to get really wacky? Make them all variables, passed from the prior page....($colA, $colB, $colC, $colD, $f1, $f2, $table, etc..) $built_statement = "select $colA, $colB, $colA"."+".$colB"."*"."0.4 " $built_statement =. "as $f1, $colC, $colC"."+"."$colD"."*"."9 as $f2" $built_statement =. " from $table where date= $SomeDateEnteredByTheUser"; $result = pg_exec($conn, $built_statement); > If I can't and end up dynamically writing the query and sumbitting that > to Postgres is this a smart way to do it? > Or should I simply query out the basic values: colA, colB, colC and colD > and then do the calculations within PHP? Either way works. <shrug>. I do lot's of my calcs in PHP, because I can quickly edit them at the same time as my display code. Other things are far too complex or repetitive to bother PHP with, so I do 'em in Pg. In general, though, you'll find your code more flexible if it's variable driven.... -Bop -- Brought to you from boop!, the dual boot Linux/Win95 Compaq Presario 1625 laptop, currently running RedHat 6.1. Your bopping may vary.
Thanks for everyone's responses :) It seems I simply wasn't able to explain what I was trying to do. I knew how to do this with PHP already. On the bright side I've decided to do things the way you think I'm doing them, rather than the way I was thinking of doing them :) How's that for cryptic? :) I'll end this thread now and not confuse the list with my rambling thoughts for the imediate future. :) Thanks once again for all the reponses. Shawn On Tue, 28 Nov 2000, Ron Chmara wrote: > shawn everett wrote: > > > What do you consider "passing a parameter to the view" to be? > > This is where Microsoft Access has twisted me :) > > Access as you may or may not know allows you to use parameters in a query: > > SELECT * FROM table WHERE table.pkey=[Enter The Primary Key]; > > The bit in [] represents a prompt to the user. They can also be filled in > > programatically. > > In PHP, that's just a variable passed from a prior page.... > > > The basic select statement for my problem is going to work as follows: > > select colA, colB, colA+colB*0.4 as f1, colC, colC+colD*9 as f2 from table > > where date=SomeDateEnteredByTheUser; > > Can I pass SomeDateEnteredByTheUser to Postgres in some way and get back > > the set of records I want? If I can is there a way to call this from PHP? > > You _are_ asking pretty much a basic PHP question. > > $result = pg_exec($conn,"select colA, colB, colA+colB*0.4 as f1, colC, colC+colD*9 > as f2 from table where date=$SomeDateEnteredByTheUser;"); > > Where you are passing "$SomeDateEnteredByTheUser" from a prior page as > a form item. > > Want to get really wacky? Make them all variables, passed from the prior > page....($colA, $colB, $colC, $colD, $f1, $f2, $table, etc..) > > > $built_statement = "select $colA, $colB, $colA"."+".$colB"."*"."0.4 " > $built_statement =. "as $f1, $colC, $colC"."+"."$colD"."*"."9 as $f2" > $built_statement =. " from $table where date= $SomeDateEnteredByTheUser"; > > $result = pg_exec($conn, $built_statement); > > > If I can't and end up dynamically writing the query and sumbitting that > > to Postgres is this a smart way to do it? > > Or should I simply query out the basic values: colA, colB, colC and colD > > and then do the calculations within PHP? > > Either way works. <shrug>. I do lot's of my calcs in PHP, because I can > quickly edit them at the same time as my display code. Other things are > far too complex or repetitive to bother PHP with, so I do 'em in Pg. In > general, though, you'll find your code more flexible if it's variable > driven.... > > -Bop > > -- > Brought to you from boop!, the dual boot Linux/Win95 Compaq Presario 1625 > laptop, currently running RedHat 6.1. Your bopping may vary. >