From: | Bricklen Anderson <banderson(at)presinet(dot)com> |
---|---|
To: | brian <brian(at)zijn-digital(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: trouble with setof record return |
Date: | 2006-10-05 17:33:53 |
Message-ID: | 45254201.3050900@presinet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
brian wrote:
> 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
>
Try casting your query cols as TEXT,
eg.
(m.first_name || '' '' || m.last_name)::TEXT AS
name,(m.organisation)::TEXT, (m.email)::TEXT AS address
From | Date | Subject | |
---|---|---|---|
Next Message | A. Kretschmer | 2006-10-05 17:39:43 | Re: trouble with setof record return |
Previous Message | John D. Burger | 2006-10-05 17:33:03 | Two efficiency questions - clustering and ints |