Postgresql add rule to column to transform data on select

Question

I want to have a column that stores data in one format (Postgis geography format), but whenever it is subjected to select query, it should be transformed with function and returned in another format.

Is it possible?

Will it affect queries like

Update table set other_column = do_comething(column_with_transform_rule)

@a_horse_with_no_name view is indeed a way to solve it, but they are not welcomed because of scripting language issues. — Nameless, Apr 04 '12 at 08:57
Scripting language issues??? Seen from the client size a view *is* a table, certainly in the SELECT case. — wildplasser, Apr 04 '12 at 09:00
@Nameless: that must be the strangest reason for not using a view I have ever heard. You should explain that in more detail on what you real problem is — , Apr 04 '12 at 09:01
@a_horse_with_no_name it is indeed seen as a table. Thing that is, we have a mapper class for each table, and if I will create a view, second mapper will be needed. Another table name, another mapper. And it goes against simplicity, because in all other aspects, it is the same and one table. — Nameless, Apr 04 '12 at 09:16
Well , you could drop the "mapper class" for the original table and use the mapper for the view instead. Same number of mappers involved. — wildplasser, Apr 04 '12 at 09:22
@wildplasser aaaaand, how would I insert/update/delete while working with a view, not with a table? — Nameless, Apr 04 '12 at 09:23
You could make INSTEAD rules for insert/update/delete, but that method is not very popular here. — wildplasser, Apr 04 '12 at 09:26
@wildplasser: an instead of *trigger* is preferred over a rule. I think the solution using a view and an instead of trigger is probably easier to manage than a select rule. — , Apr 04 '12 at 09:32
I know that it is preferred. (BTW: I beg to differ, since the rule-method involves fewer objects than the trigger-method. (3 rules versus 3 triggers + 1..3 functions)) — wildplasser, Apr 04 '12 at 09:40
Thank you, I was not even thinking in that way, because my knowledge in postgresql field was somewhat limited. Now, if you would write me an example of a view which passes insert/update/delete queries to a parent, I would gladly accept it as answer. — Nameless, Apr 04 '12 at 09:59
Ok, I'll post the RULE stuff than. I suppose @a_horse_with_no_name will come up with a trigger+function thingy. — wildplasser, Apr 04 '12 at 10:21

wildplasser · Accepted Answer · 2012-04-04T10:50:42.107

CREATE VIEW clueless AS (
    SELECT na.a, na.b,na.c
       , se.d,se.e, se.f
       , do_something_usefull ( na.g, se.i, 'clue' ) AS usefull
    FROM nameless na
    JOIN sense se ON se.clue_id = na.clue_id
    -- .. maybe more 
    );

UPDATE: after getting the requirements right, this thing can be done using rules.

SET search_path='tmp';

-- Create a table and a view on it
DROP TABLE nameless CASCADE;
CREATE TABLE nameless
        ( a INTEGER NOT NULL
        , b INTEGER NOT NULL
        , PRIMARY KEY (a,b)
        );
CREATE VIEW clueless AS (
        SELECT a, b
        , a*b AS c
         FROM nameless
        );
-- Enter some data
INSERT INTO nameless(a,b)
        SELECT a, b 
        FROM generate_series(1,5) a
        , generate_series(11,12) b
        ;

CREATE RULE clue_i AS
        ON INSERT TO clueless
        DO INSTEAD (
        INSERT INTO nameless(a,b)
        VALUES (NEW.a, NEW.b)
                ;
        );

CREATE RULE clue_d AS
        ON DELETE TO clueless
        DO INSTEAD (
        DELETE FROM nameless na
        WHERE na.a = OLD.a AND na.b = OLD.b
                ;
        );

CREATE RULE clue_u AS
        ON UPDATE TO clueless
        DO INSTEAD (
        UPDATE nameless na
        SET a = NEW.a, b = NEW.b
        WHERE na.a = OLD.a AND na.b = OLD.b
                ;
        );

SELECT * FROM clueless;

INSERT INTO clueless(a,b)
SELECT 5+src.a, src.b-10
FROM clueless src
WHERE src.a >=4
        ;

-- EXPLAIN ANALYZE
UPDATE clueless
SET b=b+20
WHERE b < 10
        ;

SELECT * FROM clueless;

Results:

NOTICE:  drop cascades to view clueless
DROP TABLE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "nameless_pkey" for table "nameless"
CREATE TABLE
CREATE VIEW
INSERT 0 10
CREATE RULE
CREATE RULE
CREATE RULE
 a | b  | c  
---+----+----
 1 | 11 | 11
 1 | 12 | 12
 2 | 11 | 22
 2 | 12 | 24
 3 | 11 | 33
 3 | 12 | 36
 4 | 11 | 44
 4 | 12 | 48
 5 | 11 | 55
 5 | 12 | 60
(10 rows)

INSERT 0 4
UPDATE 4
 a  | b  |  c  
----+----+-----
  1 | 11 |  11
  1 | 12 |  12
  2 | 11 |  22
  2 | 12 |  24
  3 | 11 |  33
  3 | 12 |  36
  4 | 11 |  44
  4 | 12 |  48
  5 | 11 |  55
  5 | 12 |  60
  9 | 21 | 189
  9 | 22 | 198
 10 | 21 | 210
 10 | 22 | 220
(14 rows)

Extra note: rules are not considered the Preferred Way to accomplish this kind of thing.

A bit too sarcastic. I do know that view is seen as a table, problem is not in that. — Nameless, Apr 04 '12 at 09:25
Well, you could have expained your problem a bit more clearly, instead of forcing us having to question you for the real issues. — wildplasser, Apr 04 '12 at 09:29

Postgresql add rule to column to transform data on select

1 Answers1