Thread: Bug with FOR ... LOOP and composite types

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

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>

> 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
>

"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>

> "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
>