Thread: 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/