asked on

How do I return all columns in a postgres function?

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

ASKER CERTIFIED SOLUTION

joshguzman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

blue44

ASKER

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;