Horton

Re: Anonymous code block with parameters

Hannu Krosing <hannu@2ndquadrant.com>

View thread View on postgr.es
From: Hannu Krosing <hannu@2ndQuadrant.com>
To: Marko Tiikkaja <marko@joh.to>, Vik Fearing <vik.fearing@dalibo.com>, Hannu Krosing <hannu@2ndQuadrant.com>, Pavel Stehule <pavel.stehule@gmail.com>, Andres Freund <andres@2ndquadrant.com>
Cc: Heikki Linnakangas <hlinnakangas@vmware.com>, Craig Ringer <craig@2ndquadrant.com>, Kalyanov Dmitry <kalyanov.dmitry@gmail.com>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2014-09-18T22:22:59Z
Lists: pgsql-hackers 
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Ü