Re: Trigger Function and Html Output - Mailing list pgsql-sql

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