Обсуждение: Trigger Function and Html Output
Hello,i find i must turn once again to the list for help, in what is probably another silly request. I have a view (made of 4 tables) and now find that users need to have the view different based on some criteria from the database. its. well. its rather icky to go into. but i can figure that part out myself. The long and short of it is this, I would like the view to return a html table. I understand that I can do this via a function and a trigger on select, massage the data, and then construct each line to simply be returned to the perl program. The only problem comes with, as far as i am aware, a trigger returns and is processed on a 'per line' basis for a select, so how would i get the view's column titles output for the table header ? I ideally want to make as much of the perl construction of the table from the database, this includes the head of the table, all and each row, and then of course closing the table 'off'. I know this sounds like a strange way to do things, but i think this is the best way to go. many thanksstefs
Stef Telford wrote: > > Hello, > i find i must turn once again to the list for help, in what > is probably another silly request. That's what the list is for, and it's only silly if you already know the answer :-) > The long and short of it is this, I would like the view to > return a html table. I understand that I can do this via a function > and a trigger on select, massage the data, and then construct > each line to simply be returned to the perl program. The only > problem comes with, as far as i am aware, a trigger returns and > is processed on a 'per line' basis for a select, so how would i > get the view's column titles output for the table header ? For this sort of stuff, I like to make as much use of Perl's DBI and HTML templating modules as I can. For the templating, it's trivial to write your own stuff for this sort of problem, but have a look at the various modules on CPAN - there'll be something there for you. I've got to admit, I try to avoid using SELECT * if I can - maybe it's just me, but I've always felt if the database changes the code needs to break. I'd rather get errors than unexpected results. IMHO of course. > I ideally want to make as much of the perl construction > of the table from the database, this includes the head of the table, > all and each row, and then of course closing the table 'off'. I know > this sounds like a strange way to do things, but i think this is the > best way to go. Definitely look at some of the general-purpose templating modules. They'll all handle tables. - Richard Huxton
Richard Huxton wrote: > That's what the list is for, and it's only silly if you already know the > answer :-) well, i do agree that the only stupid question is the one not asked but there are some questions that are universally stupid and jst hoped this wasnt one of them ;> > > The long and short of it is this, I would like the view to > > return a html table. I understand that I can do this via a function > > and a trigger on select, massage the data, and then construct > > each line to simply be returned to the perl program. The only > > problem comes with, as far as i am aware, a trigger returns and > > is processed on a 'per line' basis for a select, so how would i > > get the view's column titles output for the table header ? > > For this sort of stuff, I like to make as much use of Perl's DBI and > HTML templating modules as I can. For the templating, it's trivial to > write your own stuff for this sort of problem, but have a look at the > various modules on CPAN - there'll be something there for you. actually, I have been using the perl DBI and have written my own templating software. maybe i should try to explain more. I have a perl cgi, which until it is run doesnt know the query. nothing new there as most queries are dynamic by nature, but this query comes from the user preferences which are stored in the database. Each select works on a view, rather than hardcode the view into the perl CGI, i would rather have the table header/column titles returned as the first item as text/html (i know about the func procedure to get the table_attributes) and then all the formatting thereafter done by the database for each row. maybe i am naive in thinking this way, but surely the a database function formatting the returned string must be quicker then perl. (speaking generically of course, i conceed that there are times when the reverse is true) i -am- a perl convert. i truly am. i would jst prefer to take the massaging of data and put that into a trigger function for the database. after all, i would rather use the database then jst have it as a large flat file ;) > I've got to admit, I try to avoid using SELECT * if I can - maybe it's > just me, but I've always felt if the database changes the code needs to > break. I'd rather get errors than unexpected results. IMHO of course. i dont like select * either, but i do see that there are some justified uses for it. 'never say never' in my book ;) > > I ideally want to make as much of the perl construction > > of the table from the database, this includes the head of the table, > > all and each row, and then of course closing the table 'off'. I know > > this sounds like a strange way to do things, but i think this is the > > best way to go. > > Definitely look at some of the general-purpose templating modules. > They'll all handle tables. > thank you for the input, and if i was jst starting out i would agree with you. I cant really explain it any better than i have previously, but hopefully you will see that i want to use the database to do this. hopefully that isnt that strange a request ;) many thanks, stef
From: "Stef Telford" <stef@chronozon.artofdns.com> > Richard Huxton wrote: > > Each select works on a view, rather than hardcode the view into the > perl CGI, i would rather have the table header/column titles returned > as the first item as text/html (i know about the func procedure to get the > table_attributes) and then all the formatting thereafter done by the database > for each row. > > maybe i am naive in thinking this way, but surely the a database function > formatting the returned string must be quicker then perl. (speaking > generically of course, i conceed that there are times when the reverse > is true) Not sure you'd notice much of a speed difference - in most cases other overheads will be the deciding factor. Of course, YMMV. > thank you for the input, and if i was jst starting out i would agree with > you. I cant really explain it any better than i have previously, but > hopefully you will see that i want to use the database to do this. > > hopefully that isnt that strange a request ;) Strange or not, it's your database and you're the only one in a position to make this sort of decision. I did think that it was the psql front-end that did the HTML stuff. But - there seems to be html stuff in fe-print.c (src/interfaces/libpq) and the Perl module Pg has html output options in $result->print(...) which might be flexible enough for your needs. I must admit I've never used it, so I don't know if you can add attributes to table elements, use styles etc. HTH - Richard Huxton
On Sun, Apr 01, 2001 at 01:21:43PM -0400, Stef Telford wrote: > maybe i am naive in thinking this way, but surely the a database function > formatting the returned string must be quicker then perl. (speaking > generically of course, i conceed that there are times when the reverse > is true) Formatting data is not always so simple. You definately need to encode entities and such, and doing this from SQL could get to be a pain. Particularly when you decide that now you want this column to contain a link to some other page, or that rows should alternate colors, or whatever. Doing some of the work in the database is generally a good thing, but I think in this case it's just a hell of a lot easier in Perl because it's a more powerful language. -- Christopher Masto Senior Network Monkey NetMonger Communications chris@netmonger.net info@netmonger.net http://www.netmonger.net Free yourself, free your machine, free the daemon -- http://www.freebsd.org/