Re: proposal: row_to_array function

2015-06-23 21:57 GMT+02:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>:

> On 6/23/15 9:45 AM, Pavel Stehule wrote:
>
>>
>> 2015-06-23 1:56 GMT+02:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com
>> <mailto:Jim(dot)Nasby(at)bluetreble(dot)com>>:
>>
>>
>> On 6/22/15 2:46 AM, Pavel Stehule wrote:
>>
>>
>> FOREACH key, val IN RECORD myrow
>> LOOP
>> IF pg_typeof(val) IN ('int4', 'double precision', 'numeric')
>> THEN
>> val := val + 1; -- these variables can be mutable
>> -- or maybe in futore
>> myrow[key] := val + 1;
>> END IF;
>> END LOOP;
>>
>> What is important - "val" is automatic variable, and it can has
>> different type in any step.
>>
>> It is little bit strange, but impossible to solve, so we cannot to
>> support row[var] as right value (without immutable casting). But
>> we can
>> do it with left value.
>>
>>
>> Actually, you can (theoretically) solve it for the right value as
>> well with if val is an actual type and you have operators on that
>> type that know to search for a specific operator given the actual
>> types that are involved. So if val is int4, val + 1 becomes int4 +
>> int4.
>>
>> The problem I've run into with this is by the time you've added
>> enough casts to make this workable you've probably created a
>> situation where val + something is going to recurse back to itself.
>> I've partially solved this in [1], and intend to finish it by
>> calling back in via SPI to do the final resolution, the same way the
>> RI triggers do.
>>
>> What would be a lot better is if we had better control over function
>> and operator resolution.
>>
>> [1]
>>
>> https://github.com/decibel/variant/commit/2b99067744a405da8a325de1ebabd213106f794f#diff-8aa2db4a577ee4201d6eb9041c2a457eR846
>>
>>
>> The solution of dynamic operators changes philosophy about 180° - and I
>> afraid about a performance.
>>
>> Now if I am thinking about possibilities - probably it is solvable on
>> right side too. It needs to solve two steps:
>>
>> 1. parametrized record reference syntax - some like SELECT $1[$]
>> 2. possibility to throw plan cache, if result has different type than is
>> expected in cache.
>>
>
> Well, the other option is we allow for cases where we don't know in
> advance what the type will be. That would handle this, JSON, variant, and
> possibly some other scenarios.
>
> BTW, I think this relates to the desire to be able to do more OO-ish
> things in the database. Like "do X to all elements in this array". And to
> have actual classes, private members, real arrays of arrays. It seems like
> there's a bigger need here that's only being addressed piecemeal. :/

I would not to open this box - and I would not to throw or redesign almost
all PostgreSQL type handling system. I am sure, so it is not necessary. PL
can be relative static if the dynamic is covered by query language. The few
features can implemented without to necessity to redesign all. Still there
are other PL - and we have not force to design new Perl, JavaScript, ...

> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>