How do I return all columns in a postgres function?

blue44
blue44 used Ask the Experts™
on
Hi,

I'm using Postgres 8.3 and wrote the below function to return all matching records.  When I try to  execute the function, I get the following error:

ERROR:  a column definition list is required for functions returning "record"

Any help would be greatly appreciated.

Thanks!
CREATE OR REPLACE FUNCTION getCustomer(IN name character varying(50))
  RETURNS setof record AS
$BODY$
SELECT * FROM Customer WHERE name LIKE '%'||$1||'%';
$BODY$
  LANGUAGE 'sql' VOLATILE
  COST 100;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Many days ago I use this for my class Project, I hope can help you too.
It's forms a part of an SQL Script...

Regards Josh.
CREATE OR REPLACE FUNCTION "public"."reporte_moroso" () RETURNS SETOF "public"."orden_trabajo" AS
$body$
DECLARE
resultado orden_trabajo;
BEGIN
  for resultado in SELECT 
  public.orden_trabajo.etapa_desarrollo,
  public.orden_trabajo.id,
  public.orden_trabajo.prioridad,
  public.orden_trabajo.fecha_fin,
  public.orden_trabajo.fecha_inicio,
  public.orden_trabajo.fecha_conclucion
FROM
  public.orden_trabajo
WHERE
  public.orden_trabajo.fecha_conclucion > public.orden_trabajo.fecha_fin
ORDER BY
  public.orden_trabajo.fecha_inicio DESC
  
  
  loop   
  RETURN NEXT resultado;
  end loop;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

Open in new window

Author

Commented:
Thanks for your example.  I tried modifying my function accordingly but got this error:

ERROR:  syntax error at or near "customer"
LINE 5: results customer;
CREATE OR REPLACE FUNCTION getCustomer(IN name character varying(50))
  RETURNS setof "customer" AS
$BODY$
DECLARE
results customer;
BEGIN
	for customer in
		SELECT * FROM Customer WHERE name LIKE '%'||$1||'%'
	loop   
	RETURN NEXT results;
	end loop;
END;
$BODY$
  LANGUAGE 'sql' VOLATILE
  COST 100;

Open in new window

In the line
RETURNS setof "customer" AS

Open in new window

you need to remove the double-quotes.

Author

Commented:
Sorry for the delay in responding but I've been swamped at work.  Thanks for everyone's help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial