Re: Anonymous code block with parameters

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Ü