plpgsql dynamic queries and optional arguments

I have a table that I would like to be able to retrieve information out of
based on a combination of multiple columns and I would like to be able to do
this through a plpgsql stored procedure. Right now I have multiple stored
procedures that I am calling based on the values parameter values I pass
them and I am using static sql. The problem with this is it doesn't scale as
well as I would like it to because when I add another column of information
to the table that needs to be used for retrieval it adds another level of
combinations.

Also, when dealing with null values with static sql I use the same exact sql
statement except for the where clause containing the "column1 is null"
versus "column1 = passedvalue". Anyways, I have made a simple example
procedure and table; any help would be greatly appreciated basically I would
like to use dynamic sql instead of static but I have unsuccessfully been
able to retrieve the results of a dynamic sql statement in a pgplsql
procedure. Here is the example table and stored procedure.

CREATE TABLE public.foo

(

fooid int4 NOT NULL DEFAULT nextval('foo_fooid_seq'::regclass),

foo_date timestamp NOT NULL,

footypeid int4 NOT NULL,

footext varchar,

CONSTRAINT pk_fooid PRIMARY KEY (fooid)

)

WITHOUT OIDS;

ALTER TABLE public.foo OWNER TO fro;

CREATE OR REPLACE FUNCTION public.get_nextfoo(pfoo_date "timestamp",
pfoovalue int4, pfootext bpchar)

RETURNS SETOF public.foo AS

$BODY$DECLARE

rec foo%ROWTYPE;

BEGIN

if pfootext is null then

SELECT

*

INTO

rec

FROM

foo

WHERE

foo_date = pfoo_date

and foovalue = pfoovalue

and footext is null

For Update;

else

SELECT

*

INTO

rec

FROM

foo

WHERE

foo_date = pfoo_date

and foovalue = pfoovalue

and footext = pfootext

For Update;

end if;

RETURN NEXT rec;

return;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE;

ALTER FUNCTION public.get_nextfoo(pfoo_date "timestamp", pfoovalue int4,
pfootext bpchar) OWNER TO fro;

insert into foo(foo_date,foovalue,footext) values('2006-08-15',1,'bar');

insert into foo(foo_date,foovalue,footext) values('2006-08-14',1,'bar');

insert into foo(foo_date,foovalue,footext) values('2006-08-15',2,'bar');

insert into foo(foo_date,foovalue,footext) values('2006-08-14',2,'bar');

insert into foo(foo_date,foovalue) values('2006-08-15',1);

insert into foo(foo_date,foovalue) values('2006-08-14',1);

insert into foo(foo_date,foovalue) values('2006-08-15',2);

insert into foo(foo_date,foovalue) values('2006-08-14',2);

Thanks,
Curtis