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
>