Thread: Printing PostgreSQL reports
I can post info to PostgreSQL from a webform via JSP, and I can post reports from PostgreSQL to a webpage. Can anyone tell me how I might format a PostgreSQL report to a web page so that it will print discrete, sequenced pages? Thanks, Richard Ehrlich
I wrote a paging system that I use for all my applications that take full advantage of PostgreSQL's killer LIMIT and OFFSET features. I'd suggest you do the same, it's worked very well for me so far.. It's written in PHP and customized on a per application basis so the actual source code wouldn't do anyone much good but the basic idea is to group results X at a time, then use LIMIT X to only grab the first X records from a result set. Then on the next page you say LIMIT X OFFSET X (in this example it's more of a <Next 10 results> type of thing).. You can get creative and do pretty meat paging systems with PG. Good luck! -Mitch ----- Original Message ----- From: "Richard Ehrlich" <richard@techspt.com> To: <pgsql-general@postgresql.org> Sent: Tuesday, February 20, 2001 3:00 PM Subject: Printing PostgreSQL reports > I can post info to PostgreSQL from a webform via JSP, and I can post > reports from PostgreSQL to a webpage. Can anyone tell me how I might format > a PostgreSQL report to a web page so that it will print discrete, sequenced > pages? > > Thanks, > Richard Ehrlich > >
There are Java classes (purchased of course) that allow you to do that. I think JBuilder comes with a simplified version of JPrint or JReports or something like that. I know it's not much to go on but it should get you looking in the right place. The problem is that a web "page" has no limit to it's length and where a page starts and stops is sometimes dependant on the browser's current font size, screen resolution, etc. len morgan PS: If you find an answer, I'd sure like to know about it!
> I wrote a paging system that I use for all my applications that take full > advantage of PostgreSQL's killer LIMIT and OFFSET features. I'd suggest you > do the same, it's worked very well for me so far.. Personally I really favor the idea of using LIMIT and OFFSET, I just thought I'd add this little caution: if the table is updated between queries, the results could be thrown off. So if you need a static set of results, you'll need to do a little bit more. Greg
"Mitch Vincent" <mitch@venux.net> writes:
> ... use LIMIT X to only grab the first X records from
> a result set. Then on the next page you say LIMIT X OFFSET X (in this
> example it's more of a <Next 10 results> type of thing).
Don't forget to use an ORDER BY clause that constrains the rows into a
unique order, else you may get inconsistent results from the successive
LIMITed queries.
regards, tom lane
Yes, yes, that's very important and I can't believe I left it out.. Sorry about that. Thanks Tom.. -Mitch ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Mitch Vincent" <mitch@venux.net> Cc: <pgsql-general@postgresql.org> Sent: Tuesday, February 20, 2001 4:15 PM Subject: Re: Re: Printing PostgreSQL reports > "Mitch Vincent" <mitch@venux.net> writes: > > ... use LIMIT X to only grab the first X records from > > a result set. Then on the next page you say LIMIT X OFFSET X (in this > > example it's more of a <Next 10 results> type of thing). > > Don't forget to use an ORDER BY clause that constrains the rows into a > unique order, else you may get inconsistent results from the successive > LIMITed queries. > > regards, tom lane >
hello all:
I am attempting to create a function which will return data from a
C-function. This is what i have, and it so far has been successful. Any
help anyone can give me would be greatly appreciated.
gcc Compiler, PgSQL 7.1 beta 3
thanks,
chris
-----------------------------------------------------------------------
#include <string.h>
#include "/usr/local/pgsql/include/postgres.h"
text *
hello()
{
char data[] = "hello world";
int32 new_text_size = VARHDRSZ + sizeof(data);
text *new_text = (text *) palloc(new_text_size);
strcpy(VARDATA(new_text), data);
return new_text;
}
Chris Hayner <hayner80@astro.ocis.temple.edu> writes:
> text *
> hello()
> {
> char data[] = "hello world";
> int32 new_text_size = VARHDRSZ + sizeof(data);
> text *new_text = (text *) palloc(new_text_size);
> strcpy(VARDATA(new_text), data);
> return new_text;
> }
You forgot to set the size word of the new text object. Also, the
strcpy() looks dangerous since it will copy data[]'s trailing null,
which you do not want and did not allocate room for. In short:
text *
hello()
{
char data[] = "hello world";
int32 new_text_size = VARHDRSZ + sizeof(data);
text *new_text = (text *) palloc(new_text_size);
VARSIZE(new_text) = new_text_size;
memcpy(VARDATA(new_text), data, sizeof(data));
return new_text;
}
regards, tom lane
I wrote:
> text *
> hello()
> {
> char data[] = "hello world";
> int32 new_text_size = VARHDRSZ + sizeof(data);
> text *new_text = (text *) palloc(new_text_size);
>
> VARSIZE(new_text) = new_text_size;
> memcpy(VARDATA(new_text), data, sizeof(data));
> return new_text;
> }
Drat. Replace sizeof(data) by strlen(data) both places, and
*then* it's right. You don't want the trailing null in the
TEXT object.
regards, tom lane
This brings up a question about generating reports from PostgreSQL. I've done some looking but haven't come up with an application that runs on UNIX/BSD/Linux systems for doing general report generation. Is there any projects to create a general purpose report writer? Rod --
Tom Lane writes:
> text *
> hello()
> {
> char data[] = "hello world";
> int32 new_text_size = VARHDRSZ + sizeof(data);
> text *new_text = (text *) palloc(new_text_size);
>
> VARSIZE(new_text) = new_text_size;
> memcpy(VARDATA(new_text), data, sizeof(data));
> return new_text;
> }
Is it good practice to scribble around in data type internals? Why not
text *
hello()
{
return textin("hello world");
}
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Have you looked at phpPgAdmin (http://www.greatbridge.org/project/phppgadmin/projdisplay.php) or Webmin (http://www.webmin.com)? They both provide excellent web-based interfaces suitable for report generation and run on any UNIX platform. Brent --- "Roderick A. Anderson" <raanders@tincan.org> wrote: > This brings up a question about generating reports from > PostgreSQL. I've > done some looking but haven't come up with an application that > runs on > UNIX/BSD/Linux systems for doing general report generation. > Is there any > projects to create a general purpose report writer? > > > Rod > -- > __________________________________________________ Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices! http://auctions.yahoo.com/
Peter Eisentraut <peter_e@gmx.net> writes:
> Is it good practice to scribble around in data type internals? Why not
> text *
> hello()
> {
> return textin("hello world");
> }
Well, he could do that if he wanted, but that doesn't teach him anything
about how to prepare his own datatype, which I suppose is the real point
of the exercise.
(Actually, the above code will be *less* portable to 7.1 than the other
version, because the calling convention of textin() changed ...)
regards, tom lane
I think CURSORS would be the correct way to do it.. http://www.postgresql.org/docs/aw_pgsql_book/node142.html Jeff On Tue, 20 Feb 2001, Richard Ehrlich wrote: > I can post info to PostgreSQL from a webform via JSP, and I can post > reports from PostgreSQL to a webpage. Can anyone tell me how I might format > a PostgreSQL report to a web page so that it will print discrete, sequenced > pages? > > Thanks, > Richard Ehrlich > 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 PGP Public Key : http://bignose.hub.org/public.txt
On 22 Feb 2001, at 12:49, Jeff MacDonald wrote: > I think CURSORS would be the correct way to do it.. > > On Tue, 20 Feb 2001, Richard Ehrlich wrote: > > > I can post info to PostgreSQL from a webform via JSP, and I can post > > reports from PostgreSQL to a webpage. Can anyone tell me how I might format > > a PostgreSQL report to a web page so that it will print discrete, sequenced > > pages? > > Here the problem is in that "discrete" keyword, that doesn't fare well at all with HTML. You can't put anything that seems a pagebreak on a HTML, the browser handles the print as it prefers. The best you could do is try to estimate the lenght of the printed page and put a lot of whitespace between a page and the next, but given the variety of browsers, systems, and printers combinations, you should have a lot of luck... If you want to generate "M$ Access-like" reports, divided into pages, you'll have to resort to a different formatting language. For example, you can generate a downloadable .rtf file, or even a .pdf one. It's a lot of work, but the result is guaranteed. /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ Fabrizio Ermini Alternate E-mail: C.so Umberto, 7 faermini@tin.it loc. Meleto Valdarno Mail on GSM: (keep it short!) 52020 Cavriglia (AR) faermini@sms.tin.it
Richard Ehrlich writes: > I can post info to PostgreSQL from a webform via JSP, and I can post > reports from PostgreSQL to a webpage. Can anyone tell me how I might format > a PostgreSQL report to a web page so that it will print discrete, sequenced > pages? I think the way to go on this eventually is to format your report data in XML. You can convert that to HTML on the server or in the browser, and you can create print output through XSL formatting objects and FOP or PassiveTeX. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/