From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Mark Raynsford <co+org(dot)postgresql(at)io7m(dot)com> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Column value derived from generated column in INSERT? |
Date: | 2022-10-19 21:51:24 |
Message-ID: | 82c73b61-3028-639d-5406-e50dbc998498@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
>
>>
>> I'll handle it with multiple statements.
>>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Erik Wienhold | 2022-10-19 22:09:09 | Re: Column value derived from generated column in INSERT? |
Previous Message | Thomas Kellerer | 2022-10-19 21:39:41 | Re: COMMIT IN STORED PROCEDURE WHILE IN A LOOP |