una - manual de postgresql 10 en español pdf

Pasar un registro como argumento de función PL/pgSQL

Question

Primero, soy nuevo en pl / pgsql. Necesito para un proyecto.

Estoy atascado con este problema (simplificado).

Mi esquema db tiene una relación a m (author, books, author_books)

Ahora quiero tener una función pl / psgsql insert_book. (Sé que todos los autores definitivamente ya están en la tabla de autor, así que solo quiero pasar sus claves principales).

Este esquema de función es lo que tengo en mente.

 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;

Como argumentos, pensé en pasar un registro de tipo libro y los autores que lo escribieron. ¿Pero cómo funcionaría esto exactamente? Busqué en Google un poco y parece que no puedo resolver esto ...

Pregunta 1 : ¿El contorno de la función es "correcto" / tiene sentido?

Pregunta 2 : ¿Cómo insertar el libro de registro en el libro de tabla? ¿Tengo que revisar todos los campos del libro (título, isbn, editorial, ...) y agregarlos a una declaración INSERT INTO o hay una forma "más inteligente"?

Pregunta 3 : ¿Cómo llamaría a mi función insert_book? Encontré este ejemplo aquí (http://dbaspot.com/postgresql/206142-passing-record-function-argument-pl-pgsql.html), pero eso realmente no me ayuda. Para fines de prueba, estoy usando el shell, pero más adelante utilizaremos Java con JDBC.

Muchas gracias por su ayuda.

Answer 1

Usando unnest() y un CTE que modifica los datos (requiere Postgres 9.1 o posterior), esta puede ser una simple consulta SQL:

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

El primer CTE x es solo para la entrada de datos simplificada y no estrictamente necesaria.

SQL Fiddle.

En cuanto a tus preguntas:

Pregunta 1: ¿El contorno de la función es "correcto" / tiene sentido?

Podría ser más fácil pasar los tipos base en lugar del book tipo compuesto, pero es un enfoque perfectamente válido. Sin embargo, debes saber cómo resolver la sintaxis para los tipos complejos. Por ejemplo, tenga en cuenta el paréntesis alrededor del nombre en mi ejemplo: (x._book).* .

Una función plpgsql podría verse así:

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;

Pregunta 2: ¿Cómo insertar el libro de registro en el libro de tabla? (...) o hay una manera "más inteligente"?

La forma más inteligente es descomponer el tipo compuesto con (variable_name).* .

Como se garantiza que el tipo coincide con la table (derivado de ella), este es uno de los pocos casos en los que está perfectamente bien, no proporcionar una lista de columnas para el comando INSERT en el código persistente.

Pregunta 3: ¿Cómo llamaría a mi función insert_book? ...

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

Dentro de otras funciones plpgsql, use PERFORM lugar de SELECT si no proporciona un objetivo ( INTO foo ) para los resultados (no existentes).

Answer 2

Al pasar el tipo de datos JSON (Postgresql 9.2 o superior):

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;