pl/pgsql and arrays[]

Hello

it work on my pc

postgres=# \sf fx
CREATE OR REPLACE FUNCTION public.fx()
RETURNS SETOF integer
LANGUAGE plpgsql
AS $function$ declare g int[] = '{20}';
begin
return next g[1];
return;
end;
$function$
postgres=# select fx();
fx
----
20
(1 row)

regards

Pavel Stehule

2011/12/5 Maxim Boguk <maxim.boguk@gmail.com>:

On Mon, Dec 5, 2011 at 3:53 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:

Hello

it work on my pc

postgres=# \sf fx
CREATE OR REPLACE FUNCTION public.fx()
RETURNS SETOF integer
LANGUAGE plpgsql
AS $function$ declare g int[] = '{20}';
begin
return next g[1];
return;
end;
$function$
postgres=# select fx();
fx
----
20
(1 row)

regards

Pavel Stehule

Oh sorry.
Seems I didn't tested simple cases.

Error happened when you work with record[] types and return setof:

create table test (id serial);
insert into test select generate_series(1,10);

CREATE OR REPLACE FUNCTION _test_array()
RETURNS SETOF test
LANGUAGE plpgsql
AS $$
DECLARE
_array test[];
_row test%ROWTYPE;
BEGIN
SELECT array(SELECT test FROM test) INTO _array;

--work
--_row := _array[1];
--RETURN NEXT _row;

--also work
--RETURN QUERY SELECT (_array[1]).*;

--error
--RETURN NEXT _array[1];

--error
--RETURN NEXT (_array[1]);

--error
--RETURN NEXT (_array[1]).*;

RETURN;
END;
$$;

2011/12/5 Maxim Boguk <maxim.boguk@gmail.com>:

Some quetions about pl/pgsql and arrays[].

Is such constructions as:

RETURN NEXT array[1];

OR

SELECT val INTO array[1] FROM ...;

Should not work?

At least documentation about RETURN NEXT says:
"RETURN NEXT expression;"

I think array[1] is a valid expression.

--
Maxim Boguk
Senior Postgresql DBA.

--
Maxim Boguk
Senior Postgresql DBA.

Phone RU: +7 910 405 4718
Phone AU: +61 45 218 5678

Skype: maxim.boguk
Jabber: maxim.boguk@gmail.com

LinkedIn profile: http://nz.linkedin.com/in/maximboguk
If they can send one man to the moon... why can't they send them all?

МойКруг: http://mboguk.moikrug.ru/
Сила солому ломит, но не все в нашей жизни - солома, да и сила далеко не
все.

2011/12/5 Maxim Boguk <maxim.boguk@gmail.com>:

On Mon, Dec 5, 2011 at 3:53 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hello

it work on my pc

postgres=# \sf fx
CREATE OR REPLACE FUNCTION public.fx()
 RETURNS SETOF integer
 LANGUAGE plpgsql
AS $function$ declare g int[] = '{20}';
begin
 return next g[1];
 return;
end;
$function$
postgres=# select fx();
 fx
----
 20
(1 row)

regards

Pavel Stehule

Oh sorry.
Seems I didn't tested simple cases.

return next in function that returns composite type needs a composite
variable. Other cases are not supported there.

Regards

Pavel Stehule

Pavel Stehule <pavel.stehule@gmail.com> writes:

return next in function that returns composite type needs a composite
variable. Other cases are not supported there.

Plain "return" has the same limitation, but this really ought to be
fixed sometime. Composite types have been getting closer and closer
to first-class status since plpgsql was written.

regards, tom lane

2011/12/5 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

return next in function that returns composite type needs a composite
variable. Other cases are not supported there.

Plain "return" has the same limitation, but this really ought to be
fixed sometime.  Composite types have been getting closer and closer
to first-class status since plpgsql was written.

I sent this patch a few years ago - it was support for RETURN NEXT ROW(...)

but probably there was some issue

Regards

Pavel