Thread: views and rules

Hello folks,

I hoped to be a smart-*ss, so I created a nice view to view a specific part
of my table with joins from another table,
but when I tried to edit the data, I got the message that I needed a good
ruleset... what does that mean? what kind of rules does it mean? I cannot
create rules on views... only on tables...

Can someone help me out?

Michiel Lange <michiel@minas.demon.nl> writes:
> I cannot create rules on views

Sure you can.  Here's a trivial example.  This lets a user of view
"bar" see a subset of rows in "foo", update only some fields of
those rows, and not insert or delete anything:

regression=# create table foo (f1 int, f2 text);
CREATE TABLE
regression=# create view bar as select * from foo where f1 < 100;
CREATE VIEW
regression=# create rule bar_update as on update to bar do instead
regression-# update foo set f2 = new.f2 where f1 = old.f1;
CREATE RULE
regression=# insert into foo values(1,'ok');
INSERT 547779 1
regression=# insert into foo values(101, 'u cant see me');
INSERT 547780 1
regression=# select * from bar;
 f1 | f2
----+----
  1 | ok
(1 row)

regression=# update bar set f1 = f1+1, f2 = f2 || ' added';
UPDATE 1
regression=# select * from bar;
 f1 |    f2
----+----------
  1 | ok added
(1 row)

regression=# select * from foo;
 f1  |      f2
-----+---------------
 101 | u cant see me
   1 | ok added
(2 rows)


If you wanted to allow inserts or deletes via the view, you'd write
additional rules for that.

            regards, tom lane

Is there a way to switch databases within psql like there is
in mysql using the 'use' command?

I have two databases, one named test, the other name data, if
I do 'psql test' I'm connected to the test database, to connect
to the data database, I have to quite psql then run the command
'psql data'. It would be nice not to have to quit out of psql.

Thanks. Steve.

\c [DATABASE]

\? for help


Adler, Stephen schrieb:

>Is there a way to switch databases within psql like there is
>in mysql using the 'use' command?
>
>I have two databases, one named test, the other name data, if
>I do 'psql test' I'm connected to the test database, to connect
>to the data database, I have to quite psql then run the command
>'psql data'. It would be nice not to have to quit out of psql.
>
>Thanks. Steve.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>
>
>
>

"Adler, Stephen" <adler@bnl.gov> writes:
> I have two databases, one named test, the other name data, if
> I do 'psql test' I'm connected to the test database, to connect
> to the data database, I have to quite psql then run the command
> 'psql data'. It would be nice not to have to quit out of psql.

\c data

            regards, tom lane