Kod
CREATE TABLE mytable
(
id int4 serial,
mytext text,
tmptext text,
user_ text[],
time_ timestamptz[]
);
(
id int4 serial,
mytext text,
tmptext text,
user_ text[],
time_ timestamptz[]
);
trigger before insert or update:
Kod
CREATE OR REPLACE FUNCTION gen_modified()
RETURNS "trigger" AS
$BODY$
begin
IF new.tmp is null then return new; -- dump insert/update
END IF;
IF tg_op = 'INSERT' THEN
new.user_ := ARRAY[new.tmp];
new.time_:= ARRAY[now()];
ELSEIF tg_op = 'UPDATE' THEN
new.user_ := array_append(new.user_, new.tmp);
new.time_ := array_append(old.time_, now());
END IF;
return new;
end;
$BODY$
LANGUAGE 'plpgsql';
RETURNS "trigger" AS
$BODY$
begin
IF new.tmp is null then return new; -- dump insert/update
END IF;
IF tg_op = 'INSERT' THEN
new.user_ := ARRAY[new.tmp];
new.time_:= ARRAY[now()];
ELSEIF tg_op = 'UPDATE' THEN
new.user_ := array_append(new.user_, new.tmp);
new.time_ := array_append(old.time_, now());
END IF;
return new;
end;
$BODY$
LANGUAGE 'plpgsql';
i funkcja o której mówi artykuł: http://www.thescripts.com/forum/thread590815.html
Kod
CREATE OR REPLACE FUNCTION explode_array(in_array anyarray)
RETURNS SETOF anyelement AS
$BODY$
SELECT ($1)[s] FROM generate_series(1, array_upper($1, 1)) AS s;
$BODY$
LANGUAGE 'sql' IMMUTABLE;
RETURNS SETOF anyelement AS
$BODY$
SELECT ($1)[s] FROM generate_series(1, array_upper($1, 1)) AS s;
$BODY$
LANGUAGE 'sql' IMMUTABLE;
a teraz dodajemy wpis:
Kod
insert into mytable (mytext, tmp) values ('text1', 'robcio');
modyfiklujemy:
Kod
update mytable set tmp='hubcio' where mytext = 'text1';
i wyciągamy dane kto i kiedy:
Kod
select id, explode_array(user_) as u, explode_array(time_) as t order by id, t FROM mytable;
Łatwe i eleganckie - nieprawdaż?
pozdro
kustosz