From: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | "Oleg Serov" <serovov(at)gmail(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: Bug with FOR ... LOOP and composite types |
Date: | 2008-09-01 12:48:49 |
Message-ID: | 162867790809010548h30d189a7tc6ac8ba179cab726@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hello
2008/9/1 Oleg Serov <serovov(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Oleg Serov | 2008-09-01 13:21:08 | Bug in RETURN QUERY |
Previous Message | Oleg Serov | 2008-09-01 12:03:36 | Bug with FOR ... LOOP and composite types |