On 09/19/2014 12:14 AM, Hannu Krosing wrote:
> On 09/18/2014 10:40 PM, Marko Tiikkaja wrote:
>> On 2014-09-18 10:29 PM, Vik Fearing wrote:
>>> On 09/18/2014 10:16 PM, Hannu Krosing wrote:
>>>> I guess it proves (a little) that WITH is the right place to do these
>>>> kind of things ...
>>> I've been wanting this syntax for a few years now, so I certainly vote
>>> for it.
>> I've also been wanting do to something like:
>>
>> WITH mytyp AS (a int, b int, c int)
>> SELECT (tup).* FROM
>> (
>> SELECT CASE WHEN .. THEN ROW(1,2,3)::mytyp
>> WHEN .. THEN ROW(2,3,4)
>> ELSE ROW (3,4,5) END AS tup
>> FROM ..
>> ) ss
> +1
Though it would be even nicer to have fully in-line type definition
SELECT (tup).* FROM ( SELECT CASE WHEN .. THEN ROW(1,2,3)::(a int, b text, c int2) WHEN .. THEN ROW(2,3,4)
ELSE ROW (3,4,5) END AS tup FROM .. ) ss
or an incomplete type with names, as types can be given in ROW
SELECT (tup).* FROM ( SELECT CASE WHEN .. THEN ROW(1,2::text,3::int2)::(a, b, c) WHEN .. THEN ROW(2,3,4)
ELSE ROW (3,4,5) END AS tup FROM .. ) ss
or just normal select query syntax:
SELECT (tup).* FROM ( SELECT CASE WHEN .. THEN ROW(1 AS a,2::text AS b,3::int2 AS c) WHEN .. THEN
ROW(2,3,4) ELSE ROW (3,4,5) END AS tup FROM .. ) ss
Cheers
--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ