pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Anthony Bouvier <anthony@developware.com>
To: pgsql-sql@postgresql.org
Subject: Unexpected Return from Function
Date: Sat, 1 Dec 2001 21:00:35 -0500
Message-ID: <CBEBKIKGCCOCPDEMKBBJMEPHCAAA.anthony@developware.com> (raw)

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




view thread (3+ messages)  latest in thread

Message-ID: <CBEBKIKGCCOCPDEMKBBJMEPHCAAA.anthony@developware.com>
Permalink:  ../CBEBKIKGCCOCPDEMKBBJMEPHCAAA.anthony@developware.com/
Also on:    postgresql.org/message-id/CBEBKIKGCCOCPDEMKBBJMEPHCAAA.anthony@developware.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: anthony@developware.com
  Subject: Re: Unexpected Return from Function
  In-Reply-To: <CBEBKIKGCCOCPDEMKBBJMEPHCAAA.anthony@developware.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox