Re: [SQL] calling function from rule

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, January 23, 2003 7:01 PM
> To: Tambet Matiisen
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] calling function from rule
>
>
> "Tambet Matiisen" <t.matiisen@aprote.ee> writes:
> >> Try 7.3, we changed the rules about returned records count.
>
> > I have 7.3. When rule and action are the same, everything
> works fine.
> > Doing an insert in update rule and opposite are OK too. Problem is,
> > when I do select in insert/update/delete rule. Then the result of
> > select is returned instead of command status, even if the select
> > is done in non-instead rule and there is unconditional instead rule.
>
> Oh, I think your complaint is really about the fact that psql doesn't
> print the command status if it got any tuples (or even just a tuple
> descriptor) in the result series.  AFAICT the information returned by
> the backend is sensible in this situation: the "UPDATE 1"
> status message
> *is* returned and is available from PQcmdStatus.  psql is
> just choosing
> not to print it.  I'm not sure that that's wrong, though.
>

I still have problems calling function from rule. It seems to me, that ODBC driver also uses number of rows returned by
selectstatement, instead of using the above mentioned PQcmdStatus. My setup: 

hekotek=# create table a (id integer);
CREATE TABLE
hekotek=# create view v as select * from a;
CREATE VIEW
hekotek=# create rule r1 as on update to v do instead update a set id = new.id where id = old.id;
CREATE RULE
hekotek=# create rule r2 as on update to v where new.id > 2 do select 1;
CREATE RULE
hekotek=# insert into a values (1);
INSERT 1195706 1

Now I open the view v in Access, set id as unique identifier and try to change it from 1 to 2. Access gives me error,
thatsomeone else has changed the record. From psqlodbc.log: 

onn=144653424, query='SELECT "public"."v"."id" FROM "public"."v" '
    [ fetched 1 rows ]
conn=144653424, query='SELECT "id"  FROM "public"."v"  WHERE "id" = 1 OR "id" = 1 OR "id" = 1 OR "id" = 1 OR "id" = 1
OR"id" = 1 OR "id" = 1 OR "id" = 1 OR "id" = 1 OR "id" = 1' 
    [ fetched 1 rows ]
conn=144653424, query='SELECT "id"  FROM "public"."v"  WHERE "id" = 1'
    [ fetched 1 rows ]
conn=144653424, query='UPDATE "public"."v" SET "id"=2  WHERE "id" = 1'
    [ fetched 0 rows ]
conn=144653424, query='ROLLBACK'

When I do select, I see that id has actually changed to 2, therefore rule r1 worked. But ODBC driver returns "fetched 0
rows"for update, when it should return the command status of last instead rule, as documented in
http://www2.se.postgresql.org/users-lounge/docs/7.3/postgres/rules-status.html.

When I change the rule r2 to return two rows, it is even more apparent:

hekotek=# drop rule r2 on v;
DROP RULE
hekotek=# create rule r2 as on update to v do select 1 union select 2;
CREATE RULE

I changed once more the id from 1 to 2 in Access and this is from psqlodbc.log:

conn=144653424, query='SELECT "public"."v"."id" FROM "public"."v" '
    [ fetched 1 rows ]
conn=144653424, query='SELECT "id"  FROM "public"."v"  WHERE "id" = 1 OR "id" = 1 OR "id" = 1 OR "id" = 1 OR "id" = 1
OR"id" = 1 OR "id" = 1 OR "id" = 1 OR "id" = 1 OR "id" = 1' 
    [ fetched 1 rows ]
conn=144653424, query='SELECT "id"  FROM "public"."v"  WHERE "id" = 1'
    [ fetched 1 rows ]
conn=144653424, query='UPDATE "public"."v" SET "id"=2  WHERE "id" = 1'
    [ fetched 2 rows ]
conn=144653424, query='ROLLBACK'

Now it returns "fetched 2 rows" for the update statement and Access complains, that unique identifier is not unique.

Is it possible to change the ODBC driver to return command status as described in documentation?

  Tambet