Thread: Printing PostgreSQL reports

Printing PostgreSQL reports

From
Richard Ehrlich
Date:
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


Re: Printing PostgreSQL reports

From
"Mitch Vincent"
Date:
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
>
>


Re: Printing PostgreSQL reports

From
"Len Morgan"
Date:
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!


Re: Printing PostgreSQL reports

From
"Gregory Wood"
Date:
> 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


Re: Re: Printing PostgreSQL reports

From
Tom Lane
Date:
"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

Re: Re: Printing PostgreSQL reports

From
"Mitch Vincent"
Date:
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
>


C function woes

From
Chris Hayner
Date:
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;
 }




Re: C function woes

From
Tom Lane
Date:
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

Re: C function woes

From
Tom Lane
Date:
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

Re: Re: Printing PostgreSQL reports

From
"Roderick A. Anderson"
Date:
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
--


Re: C function woes

From
Peter Eisentraut
Date:
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/


Re: Re: Printing PostgreSQL reports

From
"Brent R. Matzelle"
Date:
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/

Re: C function woes

From
Tom Lane
Date:
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

Re: Printing PostgreSQL reports

From
Jeff MacDonald
Date:
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


Re: Printing PostgreSQL reports

From
fabrizio.ermini@sysdat.it
Date:
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

Re: Printing PostgreSQL reports

From
Peter Eisentraut
Date:
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/