PostgreSQL Discussion - plpgsql function not working

Author

plpgsql function not working

Ruff, Jeffry C. SR.

2005-04-25, 8:24 pm

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 groupna
me
==== ======= =======
=
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

John DeSoi

2005-04-25, 8:24 pm

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Michael Fuhr

2005-04-25, 8:24 pm

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(arra
y(
SELECT groupname FROM userinfo.userdb_groups WHERE username = 'jruff'
), ',');

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

John DeSoi

2005-04-25, 8:24 pm

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Sponsored Links