Thread: function returning array
I am trying to return an array from a function and don't seem to be having
luck. The function seems to work fine, but if I do assignment to an array
variable, I get null in the array elements
DECLARE results varchar[]; tmpv varchar;
BEGIN -- now call func that returns varchar[] results := parseString(''abc,def,ghi'','',''); tmpv := results[1];
RAISENOTICE '' tmpv = % '',tmpv; -- tmpv will be null.
END;
I found reference to this sort of thing not working, but the reference was
from several years ago, so I'm not sure if that is still the case or if I am
doing something wrong.
Do I need to go with returning a set instead?
Dennis S
pg-user@calico-consulting.com
"Dennis" <pg-user@calico-consulting.com> writes:
> I am trying to return an array from a function and don't seem to be having
> luck.
Seems to work for me ... what PG version are you using?
regression=# create function parseString() returns varchar[] as
regression-# 'begin return \'{abc,def,ghi}\'; end' language plpgsql;
CREATE FUNCTION
regression=# select parseString(); parsestring
---------------{abc,def,ghi}
(1 row)
regression=# create function foo() returns varchar as '
regression'# declare
regression'# results varchar[];
regression'# tmpv varchar;
regression'# begin
regression'# results := parseString();
regression'# tmpv := results[1];
regression'# RAISE NOTICE '' tmpv = % '',tmpv;
regression'# return tmpv;
regression'# end' language plpgsql;
CREATE FUNCTION
regression=# select foo();
NOTICE: tmpv = abcfoo
-----abc
(1 row)
regards, tom lane
Dennis wrote:
> I am trying to return an array from a function and don't seem to be
> having luck. The function seems to work fine, but if I do assignment to
> an array variable, I get null in the array elements
> DECLARE
> results varchar[];
> tmpv varchar;
> BEGIN
> -- now call func that returns varchar[]
> results := parseString(''abc,def,ghi'','','');
> tmpv := results[1];
> RAISE NOTICE '' tmpv = % '',tmpv; -- tmpv will be null.
>
> END;
How is parseString() defined? What Postgres version?
Joe
Tom Lane writes:
> "Dennis" <pg-user@calico-consulting.com> writes:
>> I am trying to return an array from a function and don't seem to be having
>> luck.
>
> Seems to work for me ... what PG version are you using?
pg 7.4.1
I should have listed the source for the function. Here is a simplified
parseString function and the foo that calls it.
dennis=# create or replace function parseString (varchar, varchar)
dennis-# RETURNS varchar[] AS '
dennis'# DECLARE
dennis'# pParsed varchar[];
dennis'# pString ALIAS FOR $1;
dennis'# pSeparator ALIAS FOR $2;
dennis'# begin
dennis'#
dennis'# -- example simplified
dennis'# pParsed[1] = ''blah'';
dennis'# pParsed[2] = ''hrmmph'';
dennis'#
dennis'# return pParsed;
dennis'# END;
dennis'# ' language plpgsql;
CREATE FUNCTION
dennis=#
dennis=# create or replace function foo() returns varchar as '
dennis'# declare
dennis'# results varchar[];
dennis'# tmpv varchar;
dennis'# begin
dennis'# results := parseString(''asdf'',''asdf'');
dennis'# tmpv := results[1];
dennis'# RAISE NOTICE '' tmpv = % '',tmpv;
dennis'# return tmpv;
dennis'# end' language plpgsql;
CREATE FUNCTION
dennis=# select foo();
NOTICE: tmpv = <NULL>
foo-----
(1 row)
Dennis
pg-user@calico-consulting.com
Dennis wrote:
> pg 7.4.1
>
> I should have listed the source for the function. Here is a simplified
> parseString function and the foo that calls it.
>
> dennis=# create or replace function parseString (varchar, varchar)
> dennis-# RETURNS varchar[] AS '
> dennis'# DECLARE
> dennis'# pParsed varchar[];
Make that last line: pParsed varchar[] := ''{}'';
That initializes pParsed to an *empty* array. Otherwise pParsed is NULL,
and when you attempt to extend a NULL array, e.g. "pParsed[1] =
''blah''" you still get NULL. It is similar to this:
regression=# select NULL || 'blah'; ?column?
----------
(1 row)
HTH,
Joe
Joe Conway writes:
>> dennis'# pParsed varchar[];
>
> Make that last line:
> pParsed varchar[] := ''{}'';
>
> That initializes pParsed to an *empty* array. Otherwise pParsed is NULL,
> and when you attempt to extend a NULL array, e.g. "pParsed[1] = ''blah''"
> you still get NULL.
Joe, thank you very much! It makes much sense now that you tell me, but it
was obviously not something I thought of.
Dennis
pg-user a t calico-consulting dot com