How do I return all columns in a postgres function?

blue44 used Ask the Experts™

on 2011-05-15

PostgreSQL

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^® is a registered trademark of EXPERTS EXCHANGE^®

View Solutions Only

joshguzman

Commented: 2011-05-15

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

blue44

Author

Commented: 2011-05-15

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;