Real-Time Conversion in Data Loading — Triggers and Rules

Image for post
Image for post

Background

Example 1) Rule Conversion

postgres=# create table nt(id int, c1 numeric, c2 numeric);    
CREATE TABLE
postgres=# create table nt_geo (id int, geo geometry);    
CREATE TABLE
postgres=# create rule r1 as on insert to nt do instead insert into nt_geo values (NEW.id, ST_MakePoint(NEW.c1,NEW.c2));    
CREATE RULE
postgres=# insert into nt values (1,1,1);    
INSERT 0 1
postgres=# select * from nt;    
id | c1 | c2
----+----+----
(0 rows)

postgres=# select * from nt_geo ;
id | geo
----+--------------------------------------------
1 | 0101000000000000000000F03F000000000000F03F
(1 row)

Example 2) Rule Conversion

postgres=# create table t1 (id int, info text, j jsonb);  
CREATE TABLE
postgres=# create table t2 (id int, info text, c1 int, c2 int, c3 text);  
CREATE TABLE
postgres=# create rule r1 as on insert to t1 do instead insert into t2 values (NEW.ID, NEW.INFO, ((NEW.J)->>'c1')::int, ((NEW.j)->>'c2')::int, (NEW.j)->>'c3');  
CREATE RULE
postgres=# insert into t1 values (1,'test',jsonb '{"c1":1, "c2":2, "c3":"text"}');  
INSERT 0 1
postgres=# select * from t1;
id | info | j
----+------+---
(0 rows)

postgres=# select * from t2;
id | info | c1 | c2 | c3
----+------+----+----+------
1 | test | 1 | 2 | text
(1 row)
postgres=# copy (select 1,'test',jsonb '{"c1":1, "c2":2, "c3":"text"}') to '/tmp/test';  
COPY 1
postgres=# copy t1 from '/tmp/test';
COPY 1
postgres=# select * from t1;
id | info | j
----+------+----------------------------------
1 | test | {"c1": 1, "c2": 2, "c3": "text"}
(1 row)

postgres=# select * from t2;
id | info | c1 | c2 | c3
----+------+----+----+------
1 | test | 1 | 2 | text
(1 row)

Example 3) Trigger Conversion

postgres=# drop rule r1 on t1;  
DROP RULE
postgres=# create or replace function tg() returns trigger as $$  
postgres$# declare
postgres$# begin
postgres$# insert into t2 values (NEW.ID, NEW.INFO, ((NEW.J)->>'c1')::int, ((NEW.j)->>'c2')::int, (NEW.j)->>'c3');
postgres$# return null;
postgres$# end;
postgres$# $$ language plpgsql strict;
CREATE FUNCTION
postgres=# create trigger tg before insert on t1 for each row execute procedure tg();  
CREATE TRIGGER
postgres=# truncate t1;  
TRUNCATE TABLE
postgres=# truncate t2;
TRUNCATE TABLE
postgres=# copy t1 from '/tmp/test';
COPY 0
postgres=# insert into t1 values (1,'test',jsonb '{"c1":1, "c2":2, "c3":"text"}');
INSERT 0 0
postgres=# select * from t2;
id | info | c1 | c2 | c3
----+------+----+----+------
1 | test | 1 | 2 | text
1 | test | 1 | 2 | text
(2 rows)

postgres=# select * from t1;
id | info | j
----+------+---
(0 rows)

Summary

postgres=# select regexp_split_to_array('a,b,c,d,e', ',');
regexp_split_to_array
-----------------------
{a,b,c,d,e}
(1 row)

Original Source:

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store