Обсуждение: 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