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.
