Hi!
I'm using dblink with some DBs and i'm having a few problems, more
precisely with the dblink_current_query() function.
First I create the following views on a DB:
- create or replace view users as
select *
from dblink('hostaddr=127.0.0.1 dbname=teste user=postgres
password=postgres','select * from users')
as t1(user_id int4, username varchar(20), passwd varchar(20), address
varchar(100), phone varchar(20), group_id int4);
- create or replace view utilizadores as
select *
from dblink('hostaddr=127.0.0.1 dbname=teste1 user=postgres
password=postgres','select * from utilizadores')
as t1(user_id int4, nome varchar(100), sexo char(1), idade int2,
altura int4, peso int2);
With these views I can access and manipulate the records of tables
'users' and 'utilizadores' remotely using dblink_exec() with
dblink_current_query() as parameter directly and transparently, since
the remote tables have the same name of the views. The following rule
does the trick on insert (for example):
- CREATE OR REPLACE RULE users_ins AS ON INSERT TO users
DO INSTEAD
select dblink_exec(
'hostaddr=127.0.0.1 dbname=teste user=postgres password=postgres',
dblink_current_query()
);
So far so good, now I do the following:
- create or replace view users_util as
select
us.user_id, us.username, us.passwd, ut.nome, ut.sexo, ut.idade,
ut.altura, ut.peso, us.address, us.phone, us.group_id
from
users us, utilizadores ut
where
us.user_id = ut.user_id
This creates a view that is a join of the previous views users and
utilizadores. Imagine I want to create a rule that on insert does
instead the insert on the view users:
- CREATE OR REPLACE RULE users_util_ins AS ON INSERT TO users_util
DO INSTEAD
INSERT INTO users VALUES (
NEW.user_id,
NEW.username,
NEW.passwd,
NEW.address,
NEW.phone,
NEW.group_id
)
So now if I do:
- insert into users_util (username, passwd, nome, sexo, idade, altura,
peso, address, phone, group_id) values ('prof_04', 'prof_04',
'prof_04', 'm', 45, 165, 80, 'r. da frente, nº100', '12323572', 13);
The rule should issue the query:
- insert into users values ('prof_04', 'prof_04', 'r. da frente,
nº100', '12323572', 13);
The problem is that the query being sent to table users is the same
that I perform on the users_util view...
So (finally), my question is why does this happen? Using instead on
the users_util insert rule shouldn't discard the original query and
rewrite it according to the specified on the rule?? Is this a problem
of dblink?
Sorry about the extent of my message and thanks in advance,
Joao Afonso
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
