From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Proposal: casts row to array and array to row |
Date: | 2011-10-11 13:42:04 |
Message-ID: | CAFj8pRDneHR-5uVvycPN6Ta2yFLVaT=ad_3hjfOtxdWo1AXYHw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
2011/10/11 Merlin Moncure <mmoncure(at)gmail(dot)com>:
> On Tue, Oct 11, 2011 at 8:18 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> 2011/10/11 Merlin Moncure <mmoncure(at)gmail(dot)com>:
>>> On Tue, Oct 11, 2011 at 3:40 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>>>> Hello
>>>>
>>>> A current limits of dynamic work with row types in PL/pgSQL can be
>>>> decreased with a possible casts between rows and arrays. Now we have a
>>>> lot of tools for arrays, and these tools should be used for rows too.
>>>>
>>>> postgres=# \d mypoint
>>>> Composite type "public.mypoint"
>>>> Column │ Type │ Modifiers
>>>> ────────┼─────────┼───────────
>>>> a │ integer │
>>>> b │ integer │
>>>>
>>>> postgres=# select cast(rmypoint '(10,20) as int[]);
>>>> array
>>>> ────────────
>>>> {10,20}
>>>> (1 row)
>>>>
>>>> postgres=# select cast(ARRAY[10,20] AS mypoint);
>>>> mypoint
>>>> ─────────
>>>> (10,20)
>>>> (1 row)
>>>>
>>>> What do you think about this idea?
>>>
>>> Not sure what it buys you over the syntax we already have:
>>>
>>> select row(foo[1], bar[2]);
>>> select array[(bar).a, (bar).b];
>>
>> You can do it manually for known combinations of rowtype and
>> arraytype. But proposed casts do it generally - what has sense mainly
>> for plpgsql functions or some sql functions.
>>
>>>
>>> Also, in my coding of composite types, homogeneously typed rows don't
>>> really come up that often...
>>
>> you can use everywhere text type.
>>
>> When I wrote http://stackoverflow.com/questions/7711432/how-to-set-value-of-composite-variable-field-using-dynamic-sql/7722575#7722575
>> then I had to do lot of string operations. Proposed casts
>> significantly do this simply - and it is enought general for general
>> usage.
>
> How does your approach compare to hstore? hstore to me is just
> enhanced generic container type which supports the operations you are
> trying to do. It can be trivially (as of 9.0) moved in an out of both
> arrays and record types:
for replace some value is hstore ok, but cast to arrays is more
general - you can do some tricks like table transposition, you can use
a all tricks that we have for arrays.
>
> postgres=# create type foo_t as (a int, b text, c float);
> CREATE TYPE
>
> postgres=# select row(1, 'abc', 1.0)::foo_t #= '"b"=>"def"';
> ?column?
> -----------
> (1,def,1)
> (1 row)
In some future version I would to have a general indexable types - and
then we can have a hash (hstore) in code, but casts to arrays or to
hashs can be useful - in higher languages like plpgsql or sql.
Pavel
>
> merlin
>
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2011-10-11 13:44:08 | Re: [v9.2] DROP statement reworks |
Previous Message | Tom Lane | 2011-10-11 13:36:33 | Re: Proposal: casts row to array and array to row |