|
Home > Archive > PostgreSQL Discussion > May 2005 > triggers/functions across databases
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
triggers/functions across databases
|
|
| Eric Jones 2005-05-25, 11:23 am |
| We are migrating from Informix to Postgres 7.4.7 and are having a
difficult time finding if postgres can insert/update tables across
different databases. Namely using functions/triggers when an
update/insert is performed on a table it updates/inserts in a table on a
different database. Informix can do this with no problem but we were
wondering if there is an easy way for postgres to do this. I've searched
numerous sites and found vague answers. Thanks in advance.
Eric Jones
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
| |
| Scott Marlowe 2005-05-25, 11:23 am |
| On Wed, 2005-05-25 at 07:08, Eric Jones wrote:
> We are migrating from Informix to Postgres 7.4.7 and are having a
> difficult time finding if postgres can insert/update tables across
> different databases. Namely using functions/triggers when an
> update/insert is performed on a table it updates/inserts in a table on a
> different database. Informix can do this with no problem but we were
> wondering if there is an easy way for postgres to do this. I've searched
> numerous sites and found vague answers. Thanks in advance.
Could you solve this problem using schemas in one database? OR do you
need the data to be in two physically separate locations? If so, you
might be better off using listen / notify between the two databases, or
using an external program to begin transactions in both places and
commit or roll them back together.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
| |
| Richard Huxton 2005-05-25, 11:23 am |
| Eric Jones wrote:
> We are migrating from Informix to Postgres 7.4.7 and are having a
> difficult time finding if postgres can insert/update tables across
> different databases. Namely using functions/triggers when an
> update/insert is performed on a table it updates/inserts in a table on a
> different database. Informix can do this with no problem but we were
> wondering if there is an easy way for postgres to do this. I've searched
> numerous sites and found vague answers. Thanks in advance.
If in doubt, check the manuals or contact the list.
The short answer is "no" - you can query across schemas but not across
databases.
The long answer is "maybe" - check the contrib/dblink package (in the
source distribution). That can query another database on the same or
different machine.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
| |
| Joshua D. Drake 2005-05-25, 1:23 pm |
| Eric Jones wrote:
> We are migrating from Informix to Postgres 7.4.7 and are having a
> difficult time finding if postgres can insert/update tables across
> different databases. Namely using functions/triggers when an
> update/insert is performed on a table it updates/inserts in a table on a
> different database. Informix can do this with no problem but we were
> wondering if there is an easy way for postgres to do this. I've searched
> numerous sites and found vague answers. Thanks in advance.
If you have databases that are dependent on each others data you should
probably move those databases into a new schema within one database...
See the create schema command.
Otherwise you could use dblink but it would not be my suggestion.
Sincerely,
Joshua D. Drake
>
> Eric Jones
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
| |
| Mike Nolan 2005-05-25, 1:23 pm |
| > If you have databases that are dependent on each others data you should
> probably move those databases into a new schema within one database...
That's a non-trivial task, especially if some of the tables in the
two databases have the same name.
--
Mike Nolan
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Joshua D. Drake 2005-05-25, 1:23 pm |
| Mike Nolan wrote:
>
>
> That's a non-trivial task, especially if some of the tables in the
> two databases have the same name.
Migrating from one database to another database is a non trivial task as
a whole :)
> --
> Mike Nolan
--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
|
|
|
|
|