CODEWITHSUNDEEP
postgresqlplpgsql
Sven
Sven

Reputation: 6338

Custom function: Insert object into table

I have the following function in my postgres database:

create function my_schema.create_my_book(book my_schema.book) returns my_schema.book as $$
declare
  v_book my_schema.book;
begin
  insert into my_schema.book(title, language) values (book.title, book.language) returning * into v_book;
  return v_book;
end;
$$ language plpgsql volatile;

This way I have to type out all the column names (title, language) and values (book.title, book.language). My book table is quite big so this will blow up my code by a lot, and once I add a column I will have to remember to add it to this function too.

Is there a way to directly insert the whole book my_schema.book object?

Upvotes: 0

Views: 585

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13029

Yes, here it is. You do not even need plpgsql to do this, plain sql will do (and works faster).

create or replace function my_schema.create_my_book(arg_book my_schema.book)
returns my_schema.book as
$$
  insert into my_schema.book select arg_book.* returning *;
$$ language sql volatile;

I changed the argument's name to arg_book in order to avoid possible ambiguity. And since the type of arg_book is my_schema.book this simple code adapts itself to table mutation and continues to work.

To solve the id issue

create or replace function my_schema.create_my_book(arg_book my_schema.book)
returns my_schema.book as
$$
declare
  v_book my_schema.book%rowtype;
begin
  arg_book.id := nextval('the-id-sequence-name'); 
  insert into my_schema.book select arg_book.* returning * into v_book;
  return v_book;
end;
$$ language plpgsql volatile;

which is pretty close to your initial function, just dynamic.

Upvotes: 1

Related Questions