Re: Using tables in other PostGreSQL database - Mailing list pgsql-general

> It sounds to me like you want to share a single database between
users,
> possibly using a suitable mix of schemas and roles to apply suitable
> permissions. If you don't want them to have shared access to the data
> then you can have separate databases and grant them access only to
their
> own DB.

Ok this kind of hits my issue right on the head.  One of my tasks is to
generate a summary report of metrics from the local site here.  Now each
week I need to summarize information on the product that was shipped
that week.  My IS dept exports a text report each week which contains (
some of the information on the shipped product ).  I know that another
individual uses this information for reports that he has to do.  So
right now he is parsing this data back out from the report and storing
the info in a PostGre table.  You said the point of a database was
coherent set of connected facts.  Now his use of the report is to report
to his supervisor what and how product for his business group the
factory has made and shipped each week.  Now the raw report is "ALL"
devices.  Because he is extracting it he stores the entire thing not
just his small section of it.

Now like I said I'm being tasked to report for the mfg division on how
much, how fast, and other metrics which begins with data in his
database.  Now the "coherent facts" thing would state that I shouldn't
put a mfg report info into the business group ( marketing ) groups
database.  However, he has this table in his database.  I want to use
it.  Now for me I'd store this information in it's own database and give
everybody restricted access.  This way if they need it they can get it
and they can use it.  But as of right now I can't tell one database to
look in another database and use a table that it finds there.  I think
that kind of restricts the use of data, or it promotes the duplication
of data being stored.

I can't answer to "foreign-keys" or databases on different servers
etc... due to my lack of ( sufficient { will that work } ) knowledge.
So maybe as my experience grows things will become clearer.

It's just right now I had to make my own data loader and store this data
that I know is being done by another...  From a business productivity
point of view I see this as a waste of money.

Regards,
Barry Pettis


-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Thursday, March 27, 2008 5:55 AM
To: Pettis, Barry
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Using tables in other PostGreSQL database

Pettis, Barry wrote:
> An addon????  Being self schooled in databases to me this seems to be
a
> kludge.

Ah, well, self-schooling is always a good position from which to make
sweeping generalisations.

 > If you work in a large company environment the odds that
> someone somewhere is all ready storing or collecting data that you
need
> ( by this I mean base data ) could probably be pretty high.  So why,
if
> PostGre is so old/established, is the ability to share information
> between databases have to be done through an add on.

Because the whole point of a database is to be a coherent set of
connected facts. What is your opinion on handling:
1. Differing character-sets in different databases
2. Differing locales+sorting in different databases
3. Cross-database foreign-keys and other constraints
4. Differing objects with the same names in different databases
5. Handling #1-4 when the databases are on different physical servers.

> Now let's say that another person NEEDS that very information in a
query
> or table in their own database.  Are you saying that each person needs
> to generate this.  To me the sharing of information seems to be so
basic
> that within a said postgre server, that as along as you have access to
a
> said database you should be able to say use the data stored here.  And
> that that ability should be a rudimentary ability not an addon.

It sounds to me like you want to share a single database between users,
possibly using a suitable mix of schemas and roles to apply suitable
permissions. If you don't want them to have shared access to the data
then you can have separate databases and grant them access only to their

own DB.

> Reason why I don't' have ability to install addon's onto the database.


Nobody is forcing you to. You'll have problems with procedural
languages, custom types, dictionaries, replication, GIS though.

--
   Richard Huxton
   Archonet Ltd