Skip to content

Instantly share code, notes, and snippets.

@rturowicz
Last active December 15, 2015 08:59
Show Gist options
  • Save rturowicz/5235410 to your computer and use it in GitHub Desktop.
Save rturowicz/5235410 to your computer and use it in GitHub Desktop.
postgresql: view rules (some kind of "live views")
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