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
