18

First I am really new to pl/pgsql. Need it for a project.

I am stuck with this (simplified) problem.

My db schema has a n to m relationship (author, books, author_books)

Now I want to have a pl/psgsql function insert_book. (I do know that all authors are definitely already in the author table, so I just want to pass their primary keys).

This function outline is what I have in mind.

 create or replace function insert_book(book_to_insert book, authors integer[])
  returns void as $$
begin
    -- insert book into table books
    -- for each author add an entry to author_books table
end;
 $$ language plpgsql;

As arguments I thought to pass a record of type book and the authors that wrote it. But how exactly would this work? I googled quite a bit and can't seem to figure this out...

Question 1: Is the function outline "correct"/does it make sense?

Question 2: How to insert record book into table book? Do I have to go over all fields of book (title, isbn, publisher,...) and add them to an INSERT INTO statement or is there a "smarter" way?

Question 3: How would I call my function insert_book? I found this example here (http://dbaspot.com/postgresql/206142-passing-record-function-argument-pl-pgsql.html), but that doesn't really help me. For testing purposes I am using the shell, but later on we will use Java with JDBC.

Thank you very much for your help.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
taranaki
  • 778
  • 3
  • 9
  • 28

2 Answers2

17

Using unnest() and a data-modifying CTE (requires Postgres 9.1 or later), this can be a simple SQL query:

WITH x AS (SELECT '(1,foo_book)'::book AS _book
                , '{1,2,3}'::int[]     AS _authors)
   , y AS (
   INSERT INTO book  -- no column list, correct due to composite type
   SELECT (x._book).*
   FROM   x
   RETURNING book_id
   )
INSERT INTO author_book (book_id, author_id)
SELECT y.book_id, unnest(x._authors)
FROM   x,y;  -- CROSS JOIN ok, only 1 row for x and y

The first CTE x is just for simplified data input and not strictly needed.

SQL Fiddle.

As to your questions:

Question 1: Is the function outline "correct"/does it make sense?

Might be easier to pass base types instead of the composite type book, but it is a perfectly valid approach. You have to know your way around the syntax for complex types, though. For instance, note the parenthesis around the name in my example: (x._book).*.

A plpgsql function could look like this:

CREATE OR REPLACE FUNCTION f_insert_book(_book book, _authors integer[])
   RETURNS void AS 
$func$
BEGIN
    WITH y AS (
        INSERT INTO book b
        SELECT (_book).*
        RETURNING b.book_id
        )
    INSERT INTO author_book (book_id, author_id)
    SELECT y.book_id, unnest(_authors)
    FROM   y;
END
$func$ LANGUAGE plpgsql;

Question 2: How to insert record book into table book? (...) or is there a "smarter" way?

The smarter way is to decompose the composite type with (variable_name).*.

As the type is guaranteed to match the table (being derived from it), this is one of the rare cases, where it is perfectly ok, not to provide a column list for the INSERT command in persisted code.

Question 3: How would I call my function insert_book? ...

SELECT f_insert_book('(1,foo_book)'::book, '{1,2,3}'::int[]);

Within other plpgsql functions, use PERFORM instead of SELECT if you don't provide a target (INTO foo) for the (non-existing) results.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • I'm going to have to do some more reading on CTEs. I must admit that I don't understand them. I've seen several of your posts where you utilize them, and they seem to simplify your solutions, but it's just not something even think about utilizing while working on a problem. – David S Oct 03 '12 at 23:34
  • @DavidS: For simple cases, plain old sub-queries are often a bit faster. But **CTE** are very useful for more complex operations or when you want to reuse results in multiple subqueries. **Data-modifying CTE** are great for manipulating multiple tables at once, tied together. **Recursive CTE** are basically the only way of recursive processing with plain SQL. Very powerful - but not always easy to comprehend. [The manual](http://www.postgresql.org/docs/current/interactive/queries-with.html) is a good place to get you started. – Erwin Brandstetter Oct 04 '12 at 00:25
  • Thanks Erwin... Good advice. I need to read the manual again and play with some examples. For more complex operations I tend to turn to "DO" statements that allow me to do things in a way that my brain seems to "get". I'm sure that's because I've written more lines of procedural code over that period than SQL. :) Thanks again. – David S Oct 04 '12 at 03:17
1

Passing JSON datatype (Postgresql 9.2 or higher):

CREATE OR REPLACE FUNCTION f_insert_book(_book json, _authors json)
   RETURNS void AS 
$$
BEGIN
-- insert book into table books
Insert into books values select * from json_populate_recordset(null:book, _book);
    -- for each author add an entry to author_books table
Insert into authors values select * from json_populate_recordset(null:authors, _authors);
end;
$$ language plpgsql;
LordZero
  • 11
  • 1