Re: Column value derived from generated column in INSERT?

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.klaver@aklaver.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)

> 
>>
>> I'll handle it with multiple statements.
>>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com