Redundant explicit field name/types description while select from function with return type of record
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?
<Eugen.Konkov@aldec.com> writes:
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
This is not a bug. The semantics of a query have to be determinable
without looking "inside" the bodies of functions it calls.
regards, tom lane
Eugen.Konkov@aldec.com wrote:
a column definition list is required for functions returning "record"
It seems a BUG
I don't think so. We can say it is a missing feature. As stated in [1]http://www.postgresql.org/docs/8.3/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS,
record types don't have a predefined structure -- they're placeholders.
How do you know the row structure before hand? Its structure can be
changed on-the-fly.
Are you agree with my suggestion? If so will you plan to fix this BUG?
TODO?
[1]: http://www.postgresql.org/docs/8.3/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS
http://www.postgresql.org/docs/8.3/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS
--
Euler Taveira de Oliveira
http://www.timbira.com/
On Fri, Mar 28, 2008 at 01:43:25PM -0300, Euler Taveira de Oliveira wrote:
Eugen.Konkov@aldec.com wrote:
a column definition list is required for functions returning "record"
It seems a BUGI don't think so. We can say it is a missing feature. As stated in [1],
record types don't have a predefined structure -- they're placeholders.
I was having a similar discussion with Gregory Stark about this and
hadn't realised that such small amounts of state was recorded with each
row.
How do you know the row structure before hand? Its structure can be
changed on-the-fly.
Sorry, I don't understand this comment. Could you elaborate?
Sam