Redundant explicit field name/types description while select from function with return type of record

Started by Eugen Konkovabout 18 years ago4 messagesbugs
Jump to latest
#1Eugen Konkov
Eugen.Konkov@aldec.com

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?

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Eugen Konkov (#1)
Re: Redundant explicit field name/types description while select from function with return type of record

<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

In reply to: Eugen Konkov (#1)
Re: Redundant explicit field name/types description while select from function with return type of record

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/

#4Sam Mason
sam@samason.me.uk
In reply to: Euler Taveira de Oliveira (#3)
Re: Redundant explicit field name/types description while select from function with return type of record

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 BUG

I 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