CODEWITHSUNDEEP
postgresqlstored-proceduresplpgsql
Deepak Kumar
Deepak Kumar

Reputation: 1304

How to insert declared type variable into table | Postgress

I have been working on creating a store procedure that will select data from a table, do some modification to that data, and then I need to insert that modified data into the same table. Take an example my table name is student. My procedure looks like below:

create or replace procedure student_create(p_code varchar)
language plpgsql
as $$
declare
   v_student public.student;
begin
    select * into v_student from student where code = p_code and is_latest_version is true;
    raise notice 'Value: %', v_student;
    v_student.id = uuid_generate_v4();
    v_student.version_created_at = now();
    v_student.version_updated_at = v_student.version_created_at;
    raise notice 'Value: %', v_student;
    INSERT INTO public.student VALUES(v_student);
end;$$

I am getting errors while calling this procedure:

ERROR:  column "id" is of type uuid but expression is of type hotel
LINE 1: INSERT INTO public.hotel VALUES(v_hotel)

I know I can make insert statements like I can get each value from the variable and set it like

INSERT INTO public.student VALUES(v_student.id, v_student.code, v_student.name);

But I don't want to do that because it will become tightly coupled and later if I add another column into the table then I need to add that column into this procedure as well.

Does anyone have idea how can I insert the declared type variable directly into table.

Upvotes: 0

Views: 5012

Answers (1)

jian
jian

Reputation: 4877

  1. There is no table type, there is only row composite type. Check manual 43.3.4. Row Types.
  2. use row type.
create or replace procedure student_create(p_code text)
language plpgsql
as $$
declare
   v_student public.student
begin
    for v_student in  select *  from student where code = p_code and is_latest_version is true
    loop
    v_student.id = uuid_generate_v4();
    v_student.version_created_at = now();
    v_student.version_updated_at = v_student.version_created_at;
    v_student.is_latest_version = true;
    v_student.code = p_code;
    INSERT INTO student VALUES(v_student.*);
end loop;
end;$$;

call it: call student_create('hello');
3. use update clause directly.

create or replace procedure student_create_1(p_code text)
language plpgsql as $$
BEGIN
    with a  as ( select uuid_generate_v4() as id ,
                       now() as version_created_at,
                       now() as version_updated_at,
                       p_code as "code"   from student 
                where code = p_code and is_latest_version is true)
    
     INSERT INTO student(id, version_created_at, version_updated_at, code) 
            select a.id, a.version_created_at,a.version_updated_at,a."code" from a;
    
end
$$;

call it: call student_create_1('hello');

fiddle code: here

Upvotes: 1

Related Questions