Write a PostgreSQL function that takes a string as argument and gets information about all beers that contain that string somewhere in their name (use case-insensitive matching).

create or replace function Q12(partial_name text) returns setof BeerData ... 

The BeerData type has three components:

• beer: the name of the beer
• brewer: the name of the brewery/breweries who make the beer
• info: the ingredients used in making the beer

Note that some beers involve two breweries who collaborate in making the beer. These beers should notbe shown twice, once for each brewer. Instead, the brewer column should contain the names of all breweries in alphabetical order, and separated by ' + '. There are examples of this in the Examples page.

The info should presented as a single text string, formatted as up to three lines: one containing a comma-separated list of hops, one containing a comma-separated list of grains, and one containing a comma-separated list of adjuncts. If no information is available about one of these types of ingredients, do not include a line for that type. Do not include a final ' ' character in the result string.

An example of what the info should look like for a beer that uses all ingredient types:

Hops: Bravo,Centennial,Mosaic Grain: Oats,Pale,Rye,Treticale,Wheat Extras: Lactose,Vanilla 

The comma-separated ingredient lists should be in alphabetical order.

database:

create type IngredientType as enum ('hop','grain','adjunct'); create type ContainerType as enum ('bottle','can','growler','keg'); create domain YearValue as integer check (value between 1000 and 2100); create domain MilliLiters as integer check (value > 0); create domain URLvalue as text check (value like '%.%'); -- weak check create domain ABVvalue as real check (value between 0.0 and 100.0); create domain IBUvalue as integer check (value between 0 and 200); -- Tables create table Locations ( id integer, -- would normally use serial country text not null, -- must at least know country region text, -- state or shire or ... metro text, -- metroploitan area (e.g. Sydney) town text, -- in metro area => suburb, outside metro => town primary key (id) ); create table Styles ( id integer, -- would normally use serial name text not null, -- name of style (e.g. lager, IPA) min_abv ABVvalue not null, max_abv ABVvalue not null, primary key (id), constraint minmax check (min_abv <= max_abv) ); create table Ingredients ( id integer, -- would normally use serial itype IngredientType not null, name text not null, primary key (id) ); create table Breweries ( id integer, -- would normally use serial name text not null, founded YearValue, website URLvalue, located_in integer not null references Locations(id), primary key (id) ); create table Beers ( id integer, -- would normally use serial name text not null, brewed YearValue, style integer not null references Styles(id), ABV ABVvalue not null, IBU IBUvalue, sold_in ContainerType, volume MilliLiters, notes text, rating integer not null check (rating between 0 and 10), primary key (id) ); create table Contains ( beer integer references Beers(id), ingredient integer references Ingredients(id), primary key (beer,ingredient) ); create table Brewed_by ( beer integer references Beers(id), brewery integer references Breweries(id), primary key (beer,brewery) );