Wednesday, April 28, 2010

PostgreSQL simple dblink cursor example

In PostgreSQL, dblink works fine to query the contents of a table remotely and insert into a new table. However, sometimes that table is too big to fit into memory. Here is an example function which uses cursors to do just that:

CREATE OR REPLACE FUNCTION get_data() RETURNS void AS $$
BEGIN
PERFORM dblink_connect('dbname=db hostaddr=host port=5432
user=user password=password');
PERFORM dblink_open('curs', 'select * from table');
LOOP
INSERT INTO table
SELECT data.*
FROM dblink_fetch('curs', 1)
AS data();
IF NOT FOUND THEN
EXIT;
END IF;
END LOOP;
PERFORM dblink_close('curs');
PERFORM dblink_disconnect();
END;
$$ LANGUAGE plpgsql;

1 comment:

Prashant said...

Hi Friend,

I run this query. And it was for insert.

But if you know how to update/delete records from remote database in loop.

update table set name="something" where role="admin"

Means in loop I want to update the data which only admin data from remote database.

If you know so, please tell me some guide on this