

-- a view on the Drinkers table (no phone)

create or replace view d1(drinker,suburb)
as
select name, addr
from   drinkers
;


-- a function version of the d1 view

drop type if exists dInfo cascade;
create type dInfo as (drinker text, suburb text);
 
create or replace function
	d2() returns setof dInfo
as $$
declare
	d dInfo;
begin
	for d in
		select name, addr
		from drinkers
	loop
		return next d;
	end loop;
end;
$$
language plpgsql
;
 

-- a function version of d1(), but with a parameter

create or replace function
	d3(_name text) returns setof dInfo
as $$
declare
	d dInfo;
begin
	for d in
		select name as drinker, addr as suburb
		from   drinkers
		where  name ~* _name
	loop
		return next d;
	end loop;
	if not found then
		raise notice 'No matching drinker';
	end if;
end;
$$
language plpgsql
;


-- another function version of the d1 view
 
create or replace function
	d2a() returns table(drinker text, suburb text)
as $$
declare
	d record;
begin
	for d in
		select name, addr from drinkers
	loop
		drinker := d.name;
		suburb := d.addr;
		return next;
	end loop;
end;
$$
language plpgsql
;
