Hey, I notice the following behavior but have not seen it documented anywhere. I am curious if: A. I missed the documentation B. Is poorly documented C. It's a bug If I put a function that returns a composite type into the FROM clause of a SELECT query (and it - the function - is the only source for the query) the "*" select list expands so that there is a single record for each component of the composite type. For ctype [id bigin, data varchar ] SELECT * FROM compositereturningfunction() Yields -> 2 columns (one for id and one for data) SELECT *, literal FROM compositereturningfunction() Yields -> 2 Columns (one for the composite and one for the literal) I've read quite a bit about plpgsql and composite types and do not recall anything about this specific default behavior and methods to force (workaround) to the other behavior. Specifically, there is NO WAY that I've seen to write the query so that it only outputs a single column for the composite. Also, it appears that if the function returns a single scalar and a single composite then the composite IS NOT expanded and you'd only get two columns instead of a column for each scalar and one for each part of the composite. The function I am defining is using an OUT parameter with a user-defined composite type and no explicit RETURNS type defined in the pl/pgsql function definition. I have also noticed the following does not appear to work (pl/pgsql): SELECT compositefunction() INTO compositevariable; I tried adding a ", literal" after the function and before the INTO and still get a similar result. I can provide a more detailed example if requested. *** What I am seeing is an [invalid input syntax for integer: "(92,)"] error. *** My type is defined as ( id bigint, data ctype[] ); If I execute it as; SELECT *, 1 FROM function() INTO ctype it works just fine SO... pl/pgsql: SELECT function() INTO ctype; Fails SELECT function(), 1 INTO ctype; Fails SELECT * FROM function() INTO ctype; Fails SELECT *, 1 FROM function() INTO ctype; Success - ctype is fully mapped and the '1' is ignored Interactive: SELECT function(); 1 column (ctype) SELECT function(), 1; 2 columns (ctype, '1') SELECT * FROM function(); 2 columns (ctype.1, ctype.2) SELECT *, 1 FROM function(); 2 column (ctype, '1') Is this all expected? If so did I just miss seeing (or understanding) this behavior documented. The main reason I care about being able to NOT expand the composite type is that I wish to pass the type through a function call chain without touching it so that original caller can use it in whatever form currently exists and the "creating function" and tweak its use of the composite type without requiring every possible caller of the "creating function" to change if the type has additional components added to it. David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
