pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfunction returning array
6+ messages / 3 participants
[nested] [flat]
* function returning array
@ 2004-04-13 22:41 Dennis <pg-user@calico-consulting.com>
0 siblings, 2 replies; 6+ messages in thread
From: Dennis @ 2004-04-13 22:41 UTC (permalink / raw)
To: pgsql-sql
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;
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: function returning array
@ 2004-04-14 03:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dennis <pg-user@calico-consulting.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2004-04-14 03:30 UTC (permalink / raw)
To: Dennis <pg-user@calico-consulting.com>; +Cc: pgsql-sql
"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 = abc
foo
-----
abc
(1 row)
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: function returning array
@ 2004-04-14 04:10 Joe Conway <mail@joeconway.com>
parent: Dennis <pg-user@calico-consulting.com>
1 sibling, 0 replies; 6+ messages in thread
From: Joe Conway @ 2004-04-14 04:10 UTC (permalink / raw)
To: Dennis <pg-user@calico-consulting.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: function returning array
@ 2004-04-15 12:16 Dennis <pg-user@calico-consulting.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Dennis @ 2004-04-15 12:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: function returning array
@ 2004-04-15 14:03 Dennis <pg-user@calico-consulting.com>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 6+ messages in thread
From: Dennis @ 2004-04-15 14:03 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: function returning array
@ 2004-04-15 16:10 Joe Conway <mail@joeconway.com>
parent: Dennis <pg-user@calico-consulting.com>
0 siblings, 1 reply; 6+ messages in thread
From: Joe Conway @ 2004-04-15 16:10 UTC (permalink / raw)
To: Dennis <pg-user@calico-consulting.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-04-15 16:10 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-13 22:41 function returning array Dennis <pg-user@calico-consulting.com>
2004-04-14 03:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-04-15 12:16 ` Dennis <pg-user@calico-consulting.com>
2004-04-15 16:10 ` Joe Conway <mail@joeconway.com>
2004-04-15 14:03 ` Dennis <pg-user@calico-consulting.com>
2004-04-14 04:10 ` Joe Conway <mail@joeconway.com>
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