I want a simple function that takes in all record columns and creates them if it does not exist. The table here is just a dummy, my point is more about how the id (SERIAL) column must be handled when inserting data using row types.
CREATE TABLE
some_table (
"id" SERIAL PRIMARY KEY,
"some_value" VARCHAR NOT NULL
);
declare
r_some_table_rt some_table%rowtype;
begin
select *
into r_some_table_rt
from some_table
where some_column = 'Some value';
if r_some_table_rt.id is null then
-- Tried without assigning r_some_table_rt.id
-- r_some_table_rt.id := default; -- Tried this
-- r_some_table_rt.id := currval(pg_get_serial_sequence('some_table', 'id')); -- And this
r_some_table_rt.some_column := 'Some other value';
insert into some_table
values (r_some_table_rt.*)
returning id into r_some_table_rt.id;
end if;
return r_some_table_rt.id;
end;
PostgresError: null value in column "id" of relation "some_table" violates not-null constraint
I tried the following
- Using a normal insert works perfectly fine:
insert into some_table (some_column) values ('Some value');
. r_some_table.id := default;
->PostgresError: DEFAULT is not allowed in this context
r_some_table.id := currval(pg_get_serial_sequence('some_table', 'id'));
->PostgresError: currval of sequence "some_table_id_seq" is not yet defined in this session
This worked for me:
r_some_table_rt.id := nextval(pg_get_serial_sequence('some_table', 'id'));