Re: Select + Functions + Composite Types: Behavior

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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
Sent: Saturday, February 12, 2011 5:33 PM
To: David Johnston
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Select + Functions + Composite Types: Behavior

"David Johnston" <polobo(at)yahoo(dot)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(at)postgresql(dot)org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general