| From: | brian <brian(at)zijn-digital(dot)com> | 
|---|---|
| To: | pgsql-general(at)postgresql(dot)org | 
| Subject: | trouble with setof record return | 
| Date: | 2006-10-05 17:17:41 | 
| Message-ID: | 45253E35.9050300@zijn-digital.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
Can anybody spot the problem with this function? Or, how i'm calling it? 
(it's not clear to me which it is)
CREATE TABLE member (
...
   first_name character varying(64),
   last_name character varying(64),
   organisation character varying(128),
   email character varying(128),
...
);
CREATE OR REPLACE FUNCTION fetcOnlineContacts() RETURNS SETOF record
   AS $$
DECLARE
member_contact record;
BEGIN
	
   FOR member_contact IN
     EXECUTE 'SELECT DISTINCT ON (m.email)
       m.first_name || '' '' || m.last_name AS name,
       m.organisation, m.email AS address
       FROM member AS m
       WHERE m.email IS NOT NULL
       ORDER BY m.email, m.last_name, m.organisation ASC'
   LOOP
     RETURN NEXT member_contact;
   END LOOP;
   RETURN;
	
END;
$$
   LANGUAGE plpgsql IMMUTABLE;
test=# SELECT * FROM fetchOnlineContacts() AS (name text, organisation 
text, address text);
ERROR:  wrong record type supplied in RETURN NEXT
CONTEXT:  PL/pgSQL function "fetchonlinecontacts" line 15 at return next
test=# SELECT * FROM fetchOnlineContacts() AS (name varchar(129), 
organisation varchar(128), address varchar(128));
ERROR:  wrong record type supplied in RETURN NEXT
CONTEXT:  PL/pgSQL function "fetchonlinecontacts" line 15 at return next
Normally, i declare a type, but this will be a single-use one so a 
record seemed to be sufficient.
b
| From | Date | Subject | |
|---|---|---|---|
| Next Message | John Sidney-Woollett | 2006-10-05 17:30:43 | Re: Storing images in PostgreSQL databases (again) | 
| Previous Message | Rajarshi Guha | 2006-10-05 16:52:08 | indexing for query speed - index row size exceeding btree maximum |