function accessing other db

#1

Hello everyone,

We have to copy some data from one table to another table in other database.

Is there any way to do this from inside a custom plpgsql function, with no
external applications, interfaces or storage?

Any help is appreciated.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#2

Hi,

Marcus Andree S. Magalhaes wrote:

Quote:

Hello everyone,

We have to copy some data from one table to another table in other database.

Is there any way to do this from inside a custom plpgsql function, with no
external applications, interfaces or storage?

Any help is appreciated.

use dblink from contrib.

C.

#3

On Thu, Feb 12, 2004 at 11:43:11 -0200,
"Marcus Andree S. Magalhaes" <marcus.magalhaes (AT) vlinfo (DOT) com.br> wrote:

Quote:

Hello everyone,

We have to copy some data from one table to another table in other database.

Is there any way to do this from inside a custom plpgsql function, with no
external applications, interfaces or storage?

You can do something like:

INSERT INTO tablea (col1, col2, col3) SELECT col1, col2, col3 FROM tableb;

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#4

This would work if both tables (tablea and tableb) resides on the
same database. What to do if, say tablea lives on database 'db1' and
tableb resides on another database, 'db2' ?

Thanks for your message.

Quote:

On Thu, Feb 12, 2004 at 11:43:11 -0200,
"Marcus Andree S. Magalhaes" <marcus.magalhaes (AT) vlinfo (DOT) com.br> wrote:

Hello everyone,

We have to copy some data from one table to another table in other
database.

Is there any way to do this from inside a custom plpgsql function,
with no external applications, interfaces or storage?

You can do something like:

INSERT INTO tablea (col1, col2, col3) SELECT col1, col2, col3 FROM
tableb;

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#5

On Thu, Feb 12, 2004 at 13:41:21 -0200,
"Marcus Andree S. Magalhaes" <marcus.magalhaes (AT) vlinfo (DOT) com.br> wrote:

Quote:

This would work if both tables (tablea and tableb) resides on the
same database. What to do if, say tablea lives on database 'db1' and
tableb resides on another database, 'db2' ?

Then the dblink contrib package might be of help. I haven't used it,
so I don't know for sure that it will solve your problem.

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

#6

Thanks. It seems to be exactly what I need!

Quote:

Then the dblink contrib package might be of help. I haven't used it, so
I don't know for sure that it will solve your problem.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#7

I am having problems accessing the group, what is going on here?

On 12 Feb 2004, at 16:29, Marcus Andree S. Magalhaes wrote:

Quote:

Thanks. It seems to be exactly what I need!

Then the dblink contrib package might be of help. I haven't used it,
so
I don't know for sure that it will solve your problem.

---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to
majordomo (AT) postgresql (DOT) org)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#8

Hi,

I'm reading the docs about the extended query language and the
portals. Is it possible to use portals as a kind of concurrent
cursors over a single TCP/IP connection?

Or should you execute and close portals one after another and never
execute the next portal before the previous one is closed? (Like
regular transactions.)

Thanks,

Marc

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#9

Quote:

Is it possible to use portals as a kind of concurrent cursors over a
single TCP/IP connection?

Or should you execute and close portals one after another and never
execute the next portal before the previous one is closed? (Like
regular transactions.)

Mmmmmm... Sync doesn't allow to tell which portal you want to sync,
so I guess that's it for concurrent-cursorish behavior.

Anybody know a way to implement concurrent-cursorish behavior over a
single TCP/IP connection?

Thanks,

Marc

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

#10

"M. Bastin" <marcbastin (AT) mindspring (DOT) com> writes:

Quote:

I'm reading the docs about the extended query language and the
portals. Is it possible to use portals as a kind of concurrent
cursors over a single TCP/IP connection?
Or should you execute and close portals one after another and never
execute the next portal before the previous one is closed? (Like
regular transactions.)

You can certainly have multiple portals open and fetch from different
ones in turn; not clear if that's what you meant. A portal is more like
a SQL cursor than like a transaction.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings