Avatar of blue44
blue44Flag for United States of America

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

PostgreSQL

Avatar of undefined
Last Comment
blue44
ASKER CERTIFIED SOLUTION
Avatar of joshguzman
joshguzman

Blurred text
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
Avatar of blue44
blue44
Flag of United States of America image

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
Avatar of crazedsanity
crazedsanity
Flag of United States of America image

Blurred text
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.
Avatar of blue44
blue44
Flag of United States of America image

ASKER

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

PostgreSQL is an object-relational database management system with an emphasis on extensibility and standards-compliance. As a database server, its primary function is to store data securely, supporting best practices, and to allow for retrieval at the request of other software applications. PostgreSQL implements the majority of the SQL2011 standard, is ACID-compliant and transactional using multiversion concurrency control (MVCC), and handles complex SQL queries using many indexing methods that are not available in other databases. It is cross-platform and runs on many operating systems including Linux, FreeBSD, OS X, Solaris, and Microsoft Windows.

3K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo