Hi,

I must find a value in all numeric fields in my postgres database. The
database hasn't oids. How I can create this statement.

Thx

Phil

Philipp Kraus <philipp.kraus (AT) flashpixx (DOT) de> wrote:
You mean, all numeric columns in all tables in your database??

That's a rather unusual requirement. Are you sure that this is what you
need? Depending on the size of the database, it could take forever.

I don't think it can be done in one single statement.

You'll probably have to construct SELECT statements for all tables in
the database and have to run them all.

Yours,
Laurenz Albe

Hi,

On 2007-03-16 10:15:37 +0100, Laurenz Albe <invite (AT) spam (DOT) to.invalid> said:

yes

Sure, I need this, cause we must redesign the database and wie haven't
any documentation, so I have to get the references between the
dataitems. It is'nt a statement for a view or something else, I need it
to get more information.

I know, I think I must get all tablenames from pg_tables, but how I can
get the fields from the table and theire values.


Thanks

Phil

For a similar task i choosed the approach

pg_dump, then use the Unix Toolbox (grep)

Maybe this more static approach helps.

Regards,
Nico

I think you should try to use a tool based on IR (Information Retrieval)
instead of writing a DB query. You could either write something
yourself, based on Apache Lucene, Nutch or Solr, or you could try out
DBSight (also Lucene based, but not free):

http://lucene.apache.org/
http://lucene.apache.org/nutch/
http://lucene.apache.org/solr/
http://www.dbsight.net/

If you don't need the dynamics, stick with a dump and use grep or
similar, as already proposed. Indeed, if the database is relatively
small, and the number of queries needed for the operation is limited,
you could wrap it into a script for a pseudo-dynamic approach.

--
Eivind

Philipp Kraus <philipp.kraus (AT) flashpixx (DOT) de> wrote:
I also second the suggestion to use pg_dump or other tools for this,
but to answer your question:

You get table names and schemata from information_schema.tables, you get
column information from information_schema.columns, and you get the actual
data by querying the tables themselves.

Yours,
Laurenz Albe