Re: trouble with setof record return

am Thu, dem 05.10.2006, um 13:17:41 -0400 mailte brian folgendes:
> 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),
> ...
> );
>
>

first error:

> CREATE OR REPLACE FUNCTION fetcOnlineContacts() RETURNS SETOF record
^^^^^^^^^^^^^^^^^^
>
> test=# SELECT * FROM fetchOnlineContacts() AS (name text, organisation
^^^^^^^^^^^^^^^^^^^

fetcOnlineContacts != fetchOnlineContacts

But, this isn't the problem, i get the same error. And, i have a
solution for you:

CREATE OR REPLACE FUNCTION fetcOnlineContacts(OUT name text, out organisation text, out email text) 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
name := member_contact.name;
organisation := member_contact.organisation;
email := member_contact.address;
RETURN NEXT;
END LOOP;

RETURN;

END;
$$
LANGUAGE plpgsql IMMUTABLE;

I hope, you have PG 8.1, this (with the OUT-parameter), is a feature since 8.1.

Perhaps, there are other solutions...

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net