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Ü