Thread: How to create crude report with psql and/or plpgsql

How to create crude report with psql and/or plpgsql

From
Jean-Luc Lachance
Date:
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

Re: How to create crude report with psql and/or plpgsql

From
"Andrew G. Hammond"
Date:
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

Re: How to create crude report with psql and/or plpgsql

From
Jean-Luc Lachance
Date:
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
>

Re: How to create crude report with psql and/or plpgsql

From
"Andrew G. Hammond"
Date:
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

Re: How to create crude report with psql and/or plpgsql

From
Bruce Momjian
Date:
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

Re: How to create crude report with psql and/or plpgsql

From
Jean-Luc Lachance
Date:
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.
>

Re: How to create crude report with psql and/or plpgsql

From
Bruce Momjian
Date:
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

Re: How to create crude report with psql and/or plpgsql

From
Jean-Luc Lachance
Date:
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

Re: How to create crude report with psql and/or plpgsql

From
Roberto Mello
Date:
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    
 


Re: How to create crude report with psql and/or plpgsql

From
"Ross J. Reedstrom"
Date:
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.


Re: How to create crude report with psql and/or plpgsql

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


Re: [GENERAL] How to create crude report with psql and/or plpgsql

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