Re: trouble with setof record return

A. Kretschmer wrote:
> 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
>

Yes, typo in email.


> 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

Indeed. Thanks, Andreas, Bricklen, & Joe, for the quick response. I'm
going to go with this one, i think.

b