Thread: plpgsql function not working

I found this function on line [Thanks to Jeff Eckermann and Juerg Rietmann] that takes the results of a query and creates a comma delimited  string. However when I run it I get no values. Any help would be appreciated. I apologize if this is the wrong forum.

Jeff Ruff
Tycoelectronics Power Systems

Function:
DROP FUNCTION userinfo.group_list(text);
CREATE FUNCTION userinfo.group_list(text) RETURNS text AS'
   DECLARE
        rec RECORD;
        string text := NULL;
   BEGIN
        FOR rec IN SELECT * FROM userinfo.userdb_groups WHERE username = $1 LOOP
           string := string || rec.groupname || \',\';
        END LOOP;
        string := substr(string, 1, length(string)-1);
        RETURN string;
    END;
'LANGUAGE 'plpgsql';

Acts on database listing name userdb_groups

key     username        groupname
====    ======= ========
1023    jruff           srcadm
1024    jruff           libadm
1025    jruff           mpdev
1026    jruff           systems
1027    jruff           ug
1027    jruff           cadadm
1028    jruff           mppm
1029    jruff           corerd
1030    jruff           weblive
       

Jeff Ruff
Tyco Electronics Power Systems, Inc.
CAD Support Group
phone: 972-284-4267
email: jeffry.ruff@tycoelectronics.com

On Apr 25, 2005, at 3:40 PM, Ruff, Jeffry C. SR. wrote:

> I found this function on line [Thanks to Jeff Eckermann and Juerg
> Rietmann] that takes the results of a query and creates a comma
> delimited  string. However when I run it I get no values. Any help
> would be appreciated. I apologize if this is the wrong forum.

Try changing

string text := NULL;
to
string text := '';

Any text concatenated with NULL results in a NULL value.


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

On Mon, Apr 25, 2005 at 02:40:29PM -0500, Ruff, Jeffry C. SR. wrote:
>
> CREATE FUNCTION userinfo.group_list(text) RETURNS text AS'
>    DECLARE
>     rec RECORD;
>     string text := NULL;
>    BEGIN
>     FOR rec IN SELECT * FROM userinfo.userdb_groups WHERE username = $1 LOOP
>            string := string || rec.groupname || \',\';

The result of "NULL || anything" is NULL.  Try initializing "string"
to an empty string instead of NULL.

In 7.4 and later you can use an array constructor and array_to_string():

SELECT array_to_string(array(
  SELECT groupname FROM userinfo.userdb_groups WHERE username = 'jruff'
), ',');

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Apr 25, 2005, at 4:07 PM, Ruff, Jeffry C. SR. wrote:

> Thanks for the reply. After making the change I now get the following
> ERROR:  unterminated string
> CONTEXT:  compile of PL/pgSQL function "group_list" near line 6

Ok make that

string text := '''';

I'm now spoiled with dollar quoting :)


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL