trouble with setof record return

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: Raw Message | Whole Thread | 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

Responses

Browse pgsql-general by date

  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