Thread: 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],
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


--
   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 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