Thread: How to create crude report with psql and/or plpgsql
Hi all, I did not find any kind of print statement in psql or plpgsql. So, how can I create basic report with psql and/or plpgsql? Nothing fancy, page header/footer sub-totals per page. JLL
On Tue, Mar 19, 2002 at 01:07:58PM -0500, Jean-Luc Lachance wrote: > I did not find any kind of print statement in psql or plpgsql. > So, how can I create basic report with psql and/or plpgsql? > Nothing fancy, page header/footer sub-totals per page. Errr... psql is a database query tool. There's not much available for doing that kind of thing. That said, why not just write a script to do it: #!/bin/sh echo <<END <html><head><title>This is a title</title></head> <body> <!-- header --> END # do the query psql --html --command 'SELECT * FROM foo' my_dbase echo <<END <!-- footer --> </body></html> END Of course if you really insist on doing it in psql, you could just write SELECT statements that select a preset string. Ugly, but it would work: SELECT '<html><head><title>... '::text; -- Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/ 56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481 5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1 "To blow recursion you must first blow recur" -- me
Attachment
Hello Andrew, I do not need HTML, just plain text or maybe PDF. It would be nice if there was one extra level for RAISE as in: RAISE PRINT 'Whatever' That would not send the message the log but only to the console. One could do alot with that. JLL "Andrew G. Hammond" wrote: > > On Tue, Mar 19, 2002 at 01:07:58PM -0500, Jean-Luc Lachance wrote: > > > I did not find any kind of print statement in psql or plpgsql. > > So, how can I create basic report with psql and/or plpgsql? > > Nothing fancy, page header/footer sub-totals per page. > > Errr... psql is a database query tool. There's not much available > for doing that kind of thing. That said, why not just write a > script to do it: > > #!/bin/sh > echo <<END > <html><head><title>This is a title</title></head> > <body> > <!-- header --> > END > > # do the query > psql --html --command 'SELECT * FROM foo' my_dbase > > echo <<END > <!-- footer --> > </body></html> > END >
On Tue, Mar 19, 2002 at 01:33:13PM -0500, Jean-Luc Lachance wrote: > I do not need HTML, just plain text So just do it in plain text then: #!/bin/sh echo <<END This is plain text. It's not that hard. END psql --command 'SELECT * FROM foo' my_dbase echo <<END This is more plain text. Still pretty simple, eh? END You may also want to RTFM in the psql manual about \pset and the -f option. > or maybe PDF. Please don't use bad words. PDF is a proprietary format. Beware. > It would be nice if there was one extra level for RAISE as in: > RAISE PRINT 'Whatever' > That would not send the message the log but only to the console. > One could do alot with that. Something wrong with using SELECT 'whatever'; or didn't you even read as far as the end of my first message? > "Andrew G. Hammond" wrote: > > > > On Tue, Mar 19, 2002 at 01:07:58PM -0500, Jean-Luc Lachance wrote: > > > > > I did not find any kind of print statement in psql or plpgsql. > > > So, how can I create basic report with psql and/or plpgsql? > > > Nothing fancy, page header/footer sub-totals per page. > > > > Errr... psql is a database query tool. There's not much available > > for doing that kind of thing. That said, why not just write a > > script to do it: > > > > #!/bin/sh > > echo <<END > > <html><head><title>This is a title</title></head> > > <body> > > <!-- header --> > > END > > > > # do the query > > psql --html --command 'SELECT * FROM foo' my_dbase > > > > echo <<END > > <!-- footer --> > > </body></html> > > END > > -- Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/ 56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481 5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1 "To blow recursion you must first blow recur" -- me
Attachment
Jean-Luc Lachance wrote: > Hello Andrew, > > I do not need HTML, just plain text or maybe PDF. > > It would be nice if there was one extra level for RAISE as in: > RAISE PRINT 'Whatever' > That would not send the message the log but only to the console. > One could do alot with that. 7.3 will have that. It will be RAISE INFO, which goes only to the client. RAISE LOG goes only to the server logs. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Hello Bruce, How about simply PRINT; maybe something like a printf. Just as long as there will be no prefix like 'INFO:' or 'NOTICE:' JLL Bruce Momjian wrote: > > Jean-Luc Lachance wrote: > > Hello Andrew, > > > > I do not need HTML, just plain text or maybe PDF. > > > > It would be nice if there was one extra level for RAISE as in: > > RAISE PRINT 'Whatever' > > That would not send the message the log but only to the console. > > One could do alot with that. > > 7.3 will have that. It will be RAISE INFO, which goes only to the > client. RAISE LOG goes only to the server logs. >
Jean-Luc Lachance wrote: > Hello Bruce, > > How about simply PRINT; maybe something like a printf. > > Just as long as there will be no prefix like 'INFO:' or 'NOTICE:' Indeed, there will be a prefix if INFO: or NOTICE:. No one has asked for those to be removed before. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Well, count my vote. Any body else who see the usefullness of a PRINT statement? Bruce Momjian wrote: > > Jean-Luc Lachance wrote: > > Hello Bruce, > > > > How about simply PRINT; maybe something like a printf. > > > > Just as long as there will be no prefix like 'INFO:' or 'NOTICE:' > > Indeed, there will be a prefix if INFO: or NOTICE:. No one has asked > for those to be removed before. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Tue, Mar 19, 2002 at 01:33:13PM -0500, Jean-Luc Lachance wrote: > Hello Andrew, > > I do not need HTML, just plain text or maybe PDF. > > It would be nice if there was one extra level for RAISE as in: > RAISE PRINT 'Whatever' > That would not send the message the log but only to the console. > One could do alot with that. s/alot/a lot/ You could use one of the other PL languages to do that. I'm sure the PL/*U languages do that, but perhaps the other regular PL languages would do as well (Tcl, Python, Perl). -Roberto -- +----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer
On Tue, Mar 19, 2002 at 02:42:52PM -0500, Jean-Luc Lachance wrote: > Well, count my vote. > Any body else who see the usefullness of a PRINT statement? what's wrong with: $ psql -t -c "select 'Hello World!'" template1 Hello World! $ > Bruce Momjian wrote: > > > > Jean-Luc Lachance wrote: > > > Hello Bruce, > > > > > > How about simply PRINT; maybe something like a printf. > > > > > > Just as long as there will be no prefix like 'INFO:' or 'NOTICE:' > > > > Indeed, there will be a prefix if INFO: or NOTICE:. No one has asked > > for those to be removed before.
Jean-Luc Lachance writes: > I do not need HTML, just plain text or maybe PDF. psql can generate LaTeX tables, which you can convert to PDF. -- Peter Eisentraut peter_e@gmx.net
I have found psql by itself to be quite sufficient to produce some nice looking reports. Basic approach is to create script like: SELECT 'This is my header'; SELECT <whatever data desired>; SELECT <subtotals or whatever else desired>; SELECT 'Report run at: ' || current_timestamp; (as an example footer) You can also introduce psql commands along the way to vary the appearance of the output. I have found it possible to go a long way with a simple approach like this. If you want a high degree of control over the appearance, you will need to follow one of the other suggestions made during the course of this thread. --- Jean-Luc Lachance <jllachan@nsd.ca> wrote: > Hi all, > > I did not find any kind of print statement in psql > or plpgsql. > So, how can I create basic report with psql and/or > plpgsql? > Nothing fancy, page header/footer sub-totals per > page. > > JLL > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster __________________________________________________ Do You Yahoo!? Yahoo! Sports - live college hoops coverage http://sports.yahoo.com/