Re: generated columns

On 10 September 2017 at 00:08, Jaime Casanova
<jaime.casanova@2ndquadrant.com> wrote:
>
> During my own tests, though, i found some problems:
>

a few more tests:

create table t1 (
 id serial,
 height_cm int,
 height_in int generated always as (height_cm * 10)
) ;


"""
postgres=# alter table t1 alter height_cm type numeric;
ERROR:  unexpected object depending on column: table t1 column height_in
"""
should i drop the column and recreate it after the fact? this seems
more annoying than the same problem with views (drop view & recreate),
specially after you implement STORED


"""
postgres=# alter table t1 alter height_in type numeric;
ERROR:  found unexpected dependency type 'a'
"""
uh!?


also is interesting that in triggers, both before and after, the
column has a null. that seems reasonable in a before trigger but not
in an after trigger
"""
create function f_trg1() returns trigger as $$
  begin
     raise notice '%', new.height_in;
     return new;
  end
$$ language plpgsql;

create trigger trg1 before insert on t1
for each row execute procedure f_trg1();

postgres=# insert into t1 values(default, 100);
NOTICE:  <NULL>
INSERT 0 1

create trigger trg2 after insert on t1
for each row execute procedure f_trg1();

postgres=# insert into t1 values(default, 100);
NOTICE:  <NULL>
NOTICE:  <NULL>
INSERT 0 1
"""

the default value shouldn't be dropped.
"""
postgres=# alter table t1 alter height_in drop default;
ALTER TABLE
postgres=# \d t1
                              Table "public.t1"
  Column   |  Type   | Collation | Nullable |            Default
----------------+---------+-----------+----------+--------------------------------
 id             | integer |           | not null |
nextval('t1_id_seq'::regclass)
 height_cm | integer |           |          |
 height_in   | integer |           |          | generated always as ()
"""
-- 
Jaime Casanova                      www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services