trouble with setof record return

Поиск
Список
Период
Сортировка
От brian
Тема trouble with setof record return
Дата
Msg-id 45253E35.9050300@zijn-digital.com
обсуждение исходный текст
Ответы Re: trouble with setof record return  (Bricklen Anderson <banderson@presinet.com>)
Re: trouble with setof record return  ("A. Kretschmer" <andreas.kretschmer@schollglas.com>)
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: Rajarshi Guha
Дата:
Сообщение: indexing for query speed - index row size exceeding btree maximum
Следующее
От: John Sidney-Woollett
Дата:
Сообщение: Re: Storing images in PostgreSQL databases (again)