asked on 5/16/2011

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

PostgreSQL

Last Comment

blue44

5/22/2011

ASKER CERTIFIED SOLUTION

joshguzman

5/16/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

blue44

5/16/2011

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;

Open in new window

SOLUTION

crazedsanity

5/16/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

blue44

5/22/2011

ASKER

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