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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Tom Lane Sent: Saturday, February 12, 2011 5:33 PM To: David Johnston Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: Select + Functions + Composite Types: Behavior "David Johnston" <polobo@xxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
