Re: spl function returning row type?
Eric Davies (Eric@barrodale.com) wrote:
: Its been pointed out that my original query had a goof or two in it.
: Sadly, I cut and pasted from the wrong array of my edit buffer. Argghh.
: The corrected version is below, and still suffers from the described
: problem:
:
: create row type smoo ( a integer, b integer);
:
: drop function smoo2();
: create function smoo2() returning smoo;
: define c smoo;
: let c.a = 3;
: let c.b = 5;
: return c;
: end function;
: execute function smoo2();
Check out this example:
DROP TABLE Test;
DROP FUNCTION Equal ( Foo, Foo );
DROP FUNCTION Plus ( Foo, Foo ); DROP ROW TYPE Foo RESTRICT;
CREATE ROW TYPE Foo ( a integer, b integer );
DROP FUNCTION Foo ( integer, integer );
CREATE FUNCTION Foo ( a integer, b integer )
RETURNING Foo; RETURN ROW(A,B)::Foo;
END FUNCTION;
--
-- Note the use of the ROW() type constructor. I'd guess that this
-- is slightly more efficient than the other approach.
--
EXECUTE FUNCTION Foo( 5 , 6 );
--
-- This is an ODMG OQL style 'constructor' for a type.
--
CREATE FUNCTION Plus ( arg1 Foo, Arg2 Foo )
RETURNING Foo; RETURN ROW (Arg1.A + Arg2.A, Arg1.B + Arg2.B)::Foo;
END FUNCTION;
EXECUTE FUNCTION Plus (Foo(1,2),Foo(3,4));
CREATE FUNCTION Equal ( Arg1 Foo, Arg2 Foo )
RETURNING boolean; IF (( Arg1.A = Arg2.A ) AND ( Arg1.B = Arg2.B )) THEN
RETURN 't'::boolean;
END IF;
RETURN 'f'::boolean;
END FUNCTION;
CREATE TABLE Test (
Id INTEGER NOT NULL,
First Foo NOT NULL,
Second Foo NOT NULL
);
INSERT INTO Test VALUES ( 1, Foo(1,2), Foo(3,4) );
INSERT INTO Test VALUES ( 2, Foo(3,4), Foo(1,2) );
SELECT * FROM Test;
SELECT T1.Id, T1.First, T2.Id, T2.First
FROM Test T1, Test T2
WHERE T1.First = T2.First;
-- Hope this helps!
KR
Pb