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