Thread: trouble with setof record return
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
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
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
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