Создание базы данных «Электроприборы», страница 2

FOR rec IN SELECT name,  Supplier.name_sup, Manufacturer.title, weight, price     FROM List, Types, Supplier, Manufacturer  WHERE(Types.type_ = $1 AND sup_id = Supplier.id AND manuf_id= Manufacturer.id)

LOOP

RETURN NEXT rec;

END LOOP;

END;

$$;

2. Для каждого вида электроприборов выдать список отсортированный: по дате выпуска, в алфавитном порядке по поставщику, по весу, по стоимости, по дате продажи.

CREATE FUNCTION task2a(VARCHAR) RETURNS SETOF record LANGUAGE plpgsql SECURITY DEFINER AS

$$       

DECLARE

rec RECORD;

BEGIN

FOR rec IN SELECT name, Supplier.name_sup, Manufacturer.title, weight, price, date_    FROM List, Types, Supplier, Manufacturer  WHERE(Types.type_ = $1 AND sup_id = Supplier.id AND manuf_id= Manufacturer.id) ORDER BY date_ asc

LOOP

RETURN NEXT rec;

END LOOP;

END;

$$;

CREATE FUNCTION task2b(VARCHAR) RETURNS SETOF record LANGUAGE plpgsql SECURITY DEFINER AS

$$       

DECLARE

rec RECORD;

BEGIN

FOR rec IN SELECT name, Supplier.name_sup, Manufacturer.title, weight, price     FROM List, Types, Supplier, Manufacturer  WHERE(Types.type_ = $1 AND sup_id = Supplier.id AND manuf_id= Manufacturer.id) ORDER BY name_sup asc

LOOP

RETURN NEXT rec;

END LOOP;

END;

$$;        

CREATE FUNCTION task2c(VARCHAR) RETURNS SETOF record LANGUAGE plpgsql SECURITY DEFINER AS

$$       

DECLARE

rec RECORD;

BEGIN

FOR rec IN SELECT name, Supplier.name_sup, Manufacturer.title, weight, price     FROM List, Types, Supplier, Manufacturer  WHERE(Types.type_ = $1 AND sup_id = Supplier.id AND manuf_id= Manufacturer.id) ORDER BY weight asc

LOOP

RETURN NEXT rec;

END LOOP;

END;

$$;

CREATE FUNCTION task2d(VARCHAR) RETURNS SETOF record LANGUAGE plpgsql SECURITY DEFINER AS

$$       

DECLARE

rec RECORD;

BEGIN

FOR rec IN SELECT name, Supplier.name_sup, Manufacturer.title, weight, price     FROM List, Types, Supplier, Manufacturer  WHERE(Types.type_ = $1 AND sup_id = Supplier.id AND manuf_id= Manufacturer.id) ORDER BY price asc

LOOP

RETURN NEXT rec;

END LOOP;

END;

$$;

CREATE FUNCTION task2e(VARCHAR) RETURNS SETOF record LANGUAGE plpgsql SECURITY DEFINER AS

$$       

DECLARE

rec RECORD;

BEGIN

FOR rec IN SELECT name, Supplier.name_sup, Manufacturer.title, weight, price, date_    FROM List, Types, Supplier, Manufacturer  WHERE(Types.type_ = $1 AND sup_id = Supplier.id AND manuf_id= Manufacturer.id) ORDER BY date_ asc

LOOP

RETURN NEXT rec;

END LOOP;

END;

$$;

3. Найти самый дорогой вид электроприборов, самый дешевый, среднюю стоимость по каждому виду и в целом.

CREATE FUNCTION task3a() RETURNS SETOF record LANGUAGE plpgsql SECURITY DEFINER AS

$$       

DECLARE

rec RECORD;

BEGIN

FOR rec IN SELECT name, price FROM List ORDER BY price desc LIMIT 1

LOOP

RETURN NEXT rec;

END LOOP;

END;                                                                                                                                            

$$;

CREATE FUNCTION task3b() RETURNS SETOF record LANGUAGE plpgsql SECURITY DEFINER AS

$$

DECLARE

rec RECORD;

BEGIN

FOR rec IN SELECT name, price FROM List ORDER BY price asc LIMIT 1

LOOP

RETURN NEXT rec;

END LOOP;

END;

$$;         

CREATE FUNCTION task3c() RETURNS SETOF record LANGUAGE plpgsql SECURITY DEFINER AS

$$

DECLARE

rec RECORD;

BEGIN

FOR rec IN SELECT type_, table1.sum/table1.col AS sr_pr  FROM Types, (SELECT type_id, sum(price) AS sum, count(*) AS col FROM List GROUP BY type_id) AS table1 WHERE(table1.type_id=id)

LOOP

RETURN NEXT rec;

END LOOP;

END;

$$;

CREATE FUNCTION task3d() RETURNS SETOF record LANGUAGE plpgsql SECURITY DEFINER AS

$$

DECLARE

rec RECORD;

BEGIN

FOR rec IN SELECT avg(price) AS sr_price FROM List

LOOP

RETURN NEXT rec;

END LOOP;

END;

$$;

4. Найти электроприборы с ценой в заданных пределах (предусмотреть ввод цены с клавиатуры).

CREATE FUNCTION task4(FLOAT, FLOAT) RETURNS SETOF record LANGUAGE plpgsql SECURITY DEFINER AS

$$                   

DECLARE

rec RECORD;

BEGIN

FOR rec IN SELECT name, price, Types.type_ FROM List, Types WHERE(price > $1 AND price < $2 AND type_id=Types.id)

LOOP

RETURN NEXT rec;

END LOOP;

END;

$$;

5. Найти все электроприборы заданного производителя (выбор).

CREATE FUNCTION task5(INT) RETURNS SETOF record LANGUAGE plpgsql SECURITY DEFINER AS

$$

DECLARE

rec RECORD;

BEGIN

FOR rec IN SELECT name FROM List WHERE(manuf_id = $1)