Horton

Thread

  1. Re: SQL statement : list table details

    Marc Polatschek <marc.polatschek@computec.de> — 2002-03-01T09:54:02Z

    Im using this one

SELECT 	pg_class.relname AS table_name,
		pg_attribute.attname AS column_name,
		pg_type.typname AS data_type
FROM 		pg_class,
		pg_attribute,
		pg_type
WHERE 	pg_class.relfilenode = pg_attribute.attrelid
AND 		pg_type.oid = pg_attribute.atttypid
AND 		pg_class.relname not like 'pg_%'
AND		pg_attribute.atttypmod > 0
AND		pg_class.reltype > 0
ORDER BY 	pg_class.relname, pg_attribute.attname

  2. How to create crude report with psql and/or plpgsql

    Jean-Luc Lachance <jllachan@nsd.ca> — 2002-03-19T18:07:58Z

    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

  3. Re: [SQL] How to create crude report with psql and/or plpgsql

    Andrew G. Hammond <drew@xyzzy.dhs.org> — 2002-03-19T18:21:16Z

    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

  4. Re: [SQL] How to create crude report with psql and/or plpgsql

    Jean-Luc Lachance <jllachan@nsd.ca> — 2002-03-19T18:33:13Z

    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
>

  5. Re: [SQL] How to create crude report with psql and/or plpgsql

    Andrew G. Hammond <drew@xyzzy.dhs.org> — 2002-03-19T18:41:09Z

    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

  6. Re: [SQL] How to create crude report with psql and/or plpgsql

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-03-19T18:56:50Z

    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

  7. Re: [SQL] How to create crude report with psql and/or plpgsql

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-03-19T19:18:14Z

    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

  8. Re: [SQL] How to create crude report with psql and/or plpgsql

    Jean-Luc Lachance <jllachan@nsd.ca> — 2002-03-19T19:18:51Z

    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.
>

  9. Re: [SQL] How to create crude report with psql and/or plpgsql

    Jean-Luc Lachance <jllachan@nsd.ca> — 2002-03-19T19:42:52Z

    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

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

    Roberto Mello <rmello@cc.usu.edu> — 2002-03-19T21:35:21Z

    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

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

    Ross Reedstrom <reedstrm@rice.edu> — 2002-03-19T22:12:14Z

    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.

  12. Re: [SQL] How to create crude report with psql and/or plpgsql

    Peter Eisentraut <peter_e@gmx.net> — 2002-03-19T22:45:41Z

    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

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

    Jeff Eckermann <jeff_eckermann@yahoo.com> — 2002-03-20T15:57:54Z

    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/