Функция onhand_qty

=> CREATE OR REPLACE FUNCTION onhand_qty(book books) RETURNS integer
AS $$
SELECT coalesce(sum(o.qty_change),0)::integer
FROM operations o
WHERE o.book_id = book.book_id;
$$ STABLE LANGUAGE sql;

CREATE FUNCTION

=> DROP VIEW IF EXISTS catalog_v;

DROP VIEW

=> CREATE VIEW catalog_v AS
SELECT b.book_id,
       book_name(b.book_id, b.title) AS display_name,
       b.onhand_qty
FROM   books b
ORDER BY display_name;

CREATE VIEW

Функция get_catalog

Расширяем catalog_v заголовком книги и полным списком авторов (приложение игнорирует неизвестные ему поля).

Функция, возвращающая полный список авторов:

=> CREATE OR REPLACE FUNCTION authors(book books) RETURNS text
AS $$
SELECT string_agg(a.last_name || ' ' || a.first_name || coalesce(' ' || nullif(a.middle_name,''), ''),
                  ', ' ORDER BY ash.seq_num)
FROM   authors a
       JOIN authorship ash ON a.author_id = ash.author_id
WHERE  ash.book_id = book.book_id;
$$ STABLE LANGUAGE sql;

CREATE FUNCTION

Используем эту функцию в представлении catalog_v:

=> DROP VIEW catalog_v;

DROP VIEW

=> CREATE VIEW catalog_v AS
SELECT b.book_id,
       b.title,
       b.onhand_qty,
       book_name(b.book_id, b.title) AS display_name,
       b.authors
FROM   books b
ORDER BY display_name;

CREATE VIEW

Функция get_catalog теперь использует расширенное представление:

=> CREATE OR REPLACE FUNCTION get_catalog(author_name text, book_title text, in_stock boolean)
RETURNS TABLE(book_id integer, display_name text, onhand_qty integer)
AS $$
SELECT cv.book_id, 
       cv.display_name,
       cv.onhand_qty
FROM   catalog_v cv
WHERE  cv.title   ILIKE '%'||coalesce(book_title,'')||'%'
AND    cv.authors ILIKE '%'||coalesce(author_name,'')||'%'
AND    (in_stock AND cv.onhand_qty > 0 OR in_stock IS NOT TRUE)
ORDER BY display_name;
$$ STABLE LANGUAGE sql;

CREATE FUNCTION