Last active
December 15, 2015 08:59
-
-
Save rturowicz/5235410 to your computer and use it in GitHub Desktop.
postgresql: view rules (some kind of "live views")
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE users ( | |
id int not null, | |
email varchar(255) NOT NULL, | |
pass varchar(255) NOT NULL, | |
nick varchar(128) NOT NULL, | |
PRIMARY KEY(id) | |
); | |
CREATE OR REPLACE FUNCTION dodaj_usr (int, varchar) RETURNS boolean AS | |
' | |
declare | |
_id alias for $1; | |
_nick alias for $2; | |
ret int; | |
begin | |
INSERT INTO users(id, email, pass, nick) VALUES (_id, ''emaill'', ''pass'', _nick); | |
return found; | |
end; ' | |
LANGUAGE 'plpgsql' SECURITY DEFINER; | |
create or replace view users_view as | |
select id, nick from users; | |
-- block action | |
CREATE or replace RULE users_view_ins_dummy AS ON INSERT TO users_view | |
DO INSTEAD NOTHING; | |
-- execute function | |
CREATE or replace RULE users_view_ins AS ON INSERT TO users_view | |
where NEW.id is not null and NEW.nick is not null | |
DO INSTEAD select dodaj_usr(NEW.id, NEW.nick); | |
-- execute query | |
CREATE RULE users_view_ins AS ON INSERT TO users_view | |
DO INSTEAD | |
INSERT INTO users(email, pass, nick) | |
VALUES ('[email protected]', 'haslo', NEW.nick) | |
-- conditional action | |
CREATE or replace RULE users_view_del AS ON DELETE TO users_view where nick is not null | |
DO instead DELETE FROM users WHERE nick = OLD.nick; | |
-- update action | |
CREATE RULE users_view_upd AS ON UPDATE TO users_view | |
DO INSTEAD | |
UPDATE users | |
SET nick = NEW.nick, | |
WHERE id = NEW.id; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment