Bug with FOR ... LOOP and composite types

Hello

2008/9/1 Oleg Serov <serovov@gmail.com>:

Hello.

Seems there is an error when I try to use a table with one field - composite
type, when SELECT QUERY in FOR ... LOOP instruction returns empty result.
Here are steps to reproduce:

CREATE TYPE "t_type" AS (
"a" BIGINT
);

CREATE TABLE"t_table" (
"id" BIGINT NOT NULL,
"t" "t_type",
CONSTRAINT "t_table_pkey" PRIMARY KEY("id")
) WITH OIDS;

CREATE OR REPLACE FUNCTION "t_func" () RETURNS SETOF "t_table" AS
$body$
DECLARE
rec t_table%ROWTYPE;
BEGIN
FOR rec IN
SELECT *
FROM t_table
WHERE 1=0
LOOP
RETURN NEXT rec;
END LOOP;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

SELECT * FROM t_func()

Result:

ERROR: cannot assign non-composite value to a row variable
CONTEXT: PL/pgSQL function "t_func" line 4 at FOR over SELECT rows

ROWTYPE is problem.

postgres=# CREATE OR REPLACE FUNCTION "t_func" () RETURNS SETOF "t_table" AS
postgres-# $body$
postgres$# DECLARE
postgres$# rec record;
postgres$# BEGIN
postgres$# FOR rec IN
postgres$# SELECT *
postgres$# FROM t_table
postgres$# WHERE 1=0
postgres$# LOOP
postgres$# RETURN NEXT rec;
postgres$# END LOOP;
postgres$# END;
postgres$# $body$
postgres-# LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE FUNCTION
postgres=# select * from t_func();
id | t
----+---
(0 rows)

regards
Pavel Stehule

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

2008/9/1 Oleg Serov <serovov@gmail.com>:

Seems there is an error when I try to use a table with one field - composite
type, when SELECT QUERY in FOR ... LOOP instruction returns empty result.

ROWTYPE is problem.

I think it actually is a bug. exec_for_query tries to set the target to
null this way:

exec_move_row(estate, rec, row, NULL, tuptab->tupdesc);

and exec_move_row thinks it doesn't have to present a valid "valtype"
to exec_assign_value when it's assigning a made-up null, and that fails
when the target is of PLPGSQL_DTYPE_ROW type (looks like it'd fail for
REC type too, but ROW is the case here).

We could work around the particular issue by moving the
type_is_rowtype() tests down so they're not done for a null source
value, but I think that's just a hack. A cleaner fix would be to teach
exec_move_row to present the correct column type in all cases.

regards, tom lane

But if there are some records in t_table and we romove WHERE 1=0, we will
have
ERROR: wrong record type supplied in RETURN NEXT CONTEXT: PL/pgSQL function
"t_func" line 9 at RETURN NEXT

2008/9/1 Pavel Stehule <pavel.stehule@gmail.com>

"Oleg Serov" <serovov@gmail.com> writes:

But if there are some records in t_table and we romove WHERE 1=0, we will
have
ERROR: wrong record type supplied in RETURN NEXT CONTEXT: PL/pgSQL function
"t_func" line 9 at RETURN NEXT

I couldn't reproduce that here, at least not with versions newer than
8.0. Maybe you were testing a case that also involved dropped columns?

regards, tom lane

Yes, you are right, with record type working correct;
Thanks

2008/9/2 Tom Lane <tgl@sss.pgh.pa.us>