Re: Column value derived from generated column in INSERT?

> On 19/10/2022 23:51 CEST Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> On 10/19/22 12:58 PM, Adrian Klaver wrote:
> > On 10/19/22 12:48, Mark Raynsford wrote:
> >> On 2022-10-19T12:43:31 -0700
> >> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
> >>>
> >>> HINT:  There is an entry for table "t", but it cannot be referenced from
> >>> this part of the query.
> >>>
> >>> HINT:  There is a column named "x" in table "t", but it cannot be
> >>> referenced from this part of the query.
> >>
> >> Yes, I saw those, hence asking on the list if there was a way to do it.
> >
> > Using a trigger.
>
> To expand:
>
> create table t (
> x integer not null generated always as identity,
> y integer not null
> );
> insert into t(y) values (1);
>
> select * from t;
>
> x | y
> ---+---
> 1 | 1
> (1 row)
>
>
>
> CREATE FUNCTION identity_test( )
> RETURNS trigger
> LANGUAGE plpgsql
> AS $function$
> BEGIN
> NEW.y = NEW.x * 2;
> RETURN NEW;
> END;
>
> $function$
> ;
>
> create trigger identity_trg before insert on t for each row execute
> function identity_test();
>
> insert into t(y) values (0);
>
> select * from t;
>
> x | y
> ---+---
> 1 | 1
> 2 | 4
> (2 rows)

Make t.y a generated column and avoid the trigger:

create table t (
x int not null generated always as identity,
y int not null generated always as (x * 2) stored;
);

insert into t (x) values (default), (default);

select * from t;

x | y
---+---
1 | 2
2 | 4
(2 rows)

But I think Mark wants to specify the expression in the INSERT and not define
it as part of the database schema, if I understand it correctly.

--
Erik