Thread: Select + Functions + Composite Types: Behavior

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.

"David Johnston" <polobo@yahoo.com> writes:
> 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.

> SELECT * FROM compositereturningfunction()
> Yields -> 2 columns (one for id and one for data)

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

It's just like tables. Try

    select x from compositereturningfunction() x

            regards, tom lane

SELECT * FROM compositereturningfunction() 'alias' INTO compositevariable;

Does the trick [note the '*' instead of 'x' in the select list]
If I put the name of the alias in the select list, like your example shows,
I still get the error (though honestly, I expected it to fail to execute...)

At least this doesn't LOOK like a hack/workaround...

I also do not get how "It's just like tables."  Admittedly I've just gotten
into composite types but I am well aware of the ability to alias a table in
the FROM clause but for my understanding the alias nominally makes things
easier OR is required for anonymous sub-selects (and it throws an exception
if a name is not given).  In this instance adding the alias changes behavior
which, while useful, is obscure.

Also, the behavior for those cases where the function appears in the FROM
clause makes sense to me overall; but in the
SELECT function() [INTO variable];
situation I interactively get a single column result of the proper type but
when I add the "INTO variable" (same type) It fails.  Adding a column alias
does not seem to help either (though the query/function still compiles).
Somehow (from the error) it appears that PostgreSQL is trying to assign the
entire (composite) result of the function call to the "first" component of
variable (the bigint/integer) and failing: ' invalid input syntax for
integer: "(92,)" '

All behavior noticed in 9.0.3

David J


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Saturday, February 12, 2011 5:33 PM
To: David Johnston
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Select + Functions + Composite Types: Behavior

"David Johnston" <polobo@yahoo.com> writes:
> 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.

> SELECT * FROM compositereturningfunction() Yields -> 2 columns (one
> for id and one for data)

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

It's just like tables. Try

    select x from compositereturningfunction() x

            regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom,

BTW, with the quick response you provided (THANKS!) I probably should have
pinged the list sooner in my search...

David J.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, February 12, 2011 5:33 PM
To: David Johnston
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Select + Functions + Composite Types: Behavior

"David Johnston" <polobo@yahoo.com> writes:
> 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.

> SELECT * FROM compositereturningfunction() Yields -> 2 columns (one
> for id and one for data)

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

It's just like tables. Try

    select x from compositereturningfunction() x

            regards, tom lane

Another similar situation that I'll bring here first:

I want to do:

SELECT createdid, eventresult
FROM createvehiclesaleimport() er
INTO targetid, evtresult;

But this gives a function compilation error: ' "evtresult" is not a scalar
variable '

createdid/targeted are boolean
eventresult/evtresult are CompositeType

createvehiclesaleimport() returns (Boolean, CompositeType)

I can do:

SELECT createdid, (eventresult).prop1, (eventresult).prop2
FROM createvehiclesaleimport() er
INTO targetid, evtresult.prop1, evtresult.prop2

(with or without the 'er' alias)

What's the magic syntax to allow me to chain "eventresult/evtresult" without
having to explicitly address it's scalar components.  This is different than
the other situation in that the called function now returns both a scalar as
well as a composite type whereas the other behavior occurred when the sole
output for the called function was a compositetype.

9.0.3/plpgsql

Thanks.

David J