Sorry, did not check it exactly on the 8.4, now installed 8.4 and reproduced
the issue:
BEGIN;
CREATE TYPE ta AS (a1 integer, a2 text);
CREATE TYPE tb AS (b1 integer, b2 ta);
CREATE OR REPLACE FUNCTION public.foo()
RETURNS void
LANGUAGE plpgsql
AS
$DOIT$
DECLARE
a ta;
b tb;
BEGIN
RAISE INFO 'Postgres %', version();
SELECT 1, 'a' INTO a; -- ok
RAISE INFO 'a is %', a;
SELECT ROW(10, 'a') INTO b.b2; -- ok in 8.4 but fails in 9.0 [ERROR:
invalid input syntax for integer: "(10,a)"]
RAISE INFO 'b.b2 is %', b.b2;
SELECT 100, 'a' INTO b.b2; -- ok in 9.0 but fails in 8.4 [ERROR: cannot
assign non-composite value to a row variable]
RAISE INFO 'b is %', b;
END
$DOIT$;
select public.foo();
ROLLBACK;
Best ragards,
-- Valentine Gogichashvili
On Sun, Sep 5, 2010 at 5:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Valentine Gogichashvili" <valgog@gmail.com> writes:
> > After migration to 9.0RC1 some stored procedures, working ok in 8.4 does
> not
> > work in 9.0.
> > The problem is that SELECT ROW(a,b,c) INTO var does not work any longer
> in
> > 9.0.
>
> It didn't work in 8.4 either. I tried the attached and got some variant
> of
>
> ERROR: invalid input syntax for integer: "(1,a)"
> CONTEXT: PL/pgSQL function "foo" line 9 at SQL statement
>
> in every release back to 8.1.
>
> regards, tom lane
>
>
> CREATE TYPE ta AS (a1 integer, a2 text);
> CREATE TYPE tb AS (b1 integer, b2 ta);
>
> create or replace function foo() returns void language plpgsql as $$
> DECLARE
> a ta;
> b tb;
> BEGIN
>
> SELECT 1, 'a' INTO a; -- ok
> RAISE INFO 'a is %', a;
>
> SELECT ROW(1, 'a') INTO a; -- ok in 8.4 but fails in 9.0
> RAISE INFO 'a is %', a;
>
>
> SELECT 1, 'a' INTO b.b2; -- ok
> RAISE INFO 'b is %', b;
>
> END;
> $$;
>
> select foo();
>
