Unexpected Return from Function

From: "Anthony Bouvier" <anthony(at)developware(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Unexpected Return from Function
Date: 2001-12-02 02:00:35
Message-ID: CBEBKIKGCCOCPDEMKBBJMEPHCAAA.anthony@developware.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I have a FUNCTION:

CREATE FUNCTION get_responsible(text)
RETURNS TEXT AS '
DECLARE
responsible_list text;
my_record RECORD;
BEGIN
FOR my_record IN SELECT login FROM employee WHERE id IN (1,2) LOOP
responsible_list := responsible_list || '', '' my_record.login;
END LOOP;
RETURN responsible_list;
END;
' LANGUAGE 'plpgsql';

The employee table is such:

id | login
-------------
1 | anthony
2 | mary
-------------

I expect the SQL statement "SELECT get_responsible('1,2')" to return
something like so:

get_responsible
---------------
anthony, mary
---------------

But instead I receive:

get_responsible
---------------

---------------

If I get rid of the concatenation, like so:

CREATE FUNCTION get_responsible(text)
RETURNS TEXT AS '
DECLARE
responsible_list text;
my_record RECORD;
BEGIN
FOR my_record IN SELECT login FROM employee WHERE id IN (1,2) LOOP
responsible_list := my_record.login;
END LOOP;
RETURN responsible_list;
END;
' LANGUAGE 'plpgsql';

I receive last result (for id = 2), like so:

get_responsible
---------------
mary
---------------

The SELECT statement itself runs fine, so I know it is returning two
records.

Also, the reason I am passing a 'text' datatype to the function, is
because I'd ultimately like to have the "WHERE id IN" statement to be
dynamic, like so:

CREATE FUNCTION get_responsible(text)
RETURNS TEXT AS '
DECLARE
responsible_list text;
my_record RECORD;
BEGIN
FOR my_record IN SELECT login FROM employee WHERE id IN ($1) LOOP
responsible_list := my_record.login;
END LOOP;
RETURN responsible_list;
END;
' LANGUAGE 'plpgsql';

If anyone can help me with this, I'd be much appreciative. I've been
trying combination after combination of things to try and resolve this
for the past 6 and a half hours.

Thanks,

Anthony "pulling his hair out" Bouvier

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Stephan Szabo 2001-12-02 02:23:10 Re: Unexpected Return from Function
Previous Message James Thornton 2001-12-01 09:44:52 update returns 1, but no changes have been made