Questions about Views, Rules and DBLink

From: Joao Afonso <joaoaafonso(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Questions about Views, Rules and DBLink
Date: 2005-07-31 21:51:54
Message-ID: ce04e69f0507311451685786fe@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message William Bug 2005-08-01 03:22:08 Re: Tool for database design documentation?
Previous Message eoghan 2005-07-31 21:32:04 postmaster