Printing PostgreSQL reports

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

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

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;
}

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

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/