From: | <Eugen(dot)Konkov(at)aldec(dot)com> |
---|---|
To: | <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Redundant explicit field name/types description while select from function with return type of record |
Date: | 2008-03-28 09:56:41 |
Message-ID: | 034201c890ba$068adde0$1200a8c0@kharkov.localhost |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
PG v8.3.1
CREATE or REPLACE FUNCTION "public"."aaa"()
RETURNS SETOF "pg_catalog"."record" AS
$BODY$
DECLARE r record;
BEGIN
select 1 as num into r; -- here PG know that first field is integer and has
name 'num'
return next r;
return;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
select * from aaa()
Expected result:
num
--------
1
type of field 'num' is integer;
Actual result:
pg require explicit name and type
select * from aaa() as ( num integer )
^^^^^^^^^^^^^
//////
this is redundant character typing
Also I see that PG alway know type of field in function, because of when I
write
select * from aaa() as ( num varchar )
I get an error.
SQL State: 42804
ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function "aaa" line 4 at RETURN NEXT
So there is posible to PG do not supply type explicitly. So now I can just
write:
select * from aaa() as ( num )
Futher optimizations:
When somebody write
select 1;
PG return will:
?column?
--------
1
And I have no any errors because of I do not write return data type.
Also notice that PG generate automatically a name for my field. Do you see?
Let's me extend that example:
select 1, 'asdf';
?column? | ?column?_1
--------------------
1 | asdf
Do you see an alignment of data? Right alignment - integer, left aligment -
string
PG see types of data without any problem and errors reporting
Let's my extend this example to function:
CREATE or REPLACE FUNCTION "public"."aaa"()
RETURNS SETOF "pg_catalog"."record" AS
$BODY$
DECLARE r record;
BEGIN
select 1, 'asdf'; -- as we saw earlier PG know that first field is integer
and second one is string
return next r;
return;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Keeping in mind examples above expected results for:
select * from aaa();
must be:
?column? | ?column?_1
-----------------------
1 | asdf
Actual result is:
a column definition list is required for functions returning "record"
It seems a BUG
Are you agree with my suggestion? If so will you plan to fix this BUG?
From | Date | Subject | |
---|---|---|---|
Next Message | NikhilS | 2008-03-28 10:07:18 | Re: Problem identifying constraints which should not be inherited |
Previous Message | University of Bridgeport | 2008-03-28 05:31:50 | University of Bridgeport Graduate Programs and Research Opportunities |