Re: Allow SQL/plpgsql functions to accept record

On 04/27/2015 10:35 PM, Jim Nasby wrote:
> On 4/25/15 4:50 PM, Tom Lane wrote:
>> Well, we already support local variables of type RECORD in plpgsql, so
>> it's not immediately clear to me that function arguments would be much
>> worse. There are a lot of deficiencies with the RECORD-local-variable
>> implementation: if you try to change the actual RECORD type from one
>> call
>> to the next you'll probably have problems. But it seems like we could
>> avoid that for function arguments by treating RECORD as a polymorphic
>> argument type, and thereby generating a separate set of plan trees for
>> each actual record type passed to the function within a given session.
>> So in principle it ought to work better than the local-variable case
>> does
>> today.
>>
>> In short I suspect that Jim is right and this has more to do with a
>> shortage of round tuits than any fundamental problem.
>
> I took a stab at plpgsql and it seems to work ok... but I'm not sure
> it's terribly valuable because you end up with an anonymous record
> instead of something that points back to what you handed it. The
> 'good' news is it doesn't seem to blow up on successive calls with
> different arguments...
>
>> Not sure about the SQL-function case. That might be even easier because
>> functions.c doesn't try to cache plans across queries; or maybe not.
>
> This on the other hand was rather easy. It's not horribly useful due
> to built-in restrictions on dealing with record, but that's certainly
> not plsql's fault, and this satisfies my initial use case of
>
> create function cn(record) returns bigint language sql as $$
> SELECT count(*)
> FROM json_each_text( row_to_json($1) ) a
> WHERE value IS NULL $$;
>
> Attached patches both pass make check. The plpgsql is WIP, but I think
> the SQL one is OK.

My point remains that we really need methods of a) getting the field
names from generic records and b) using text values to access fields of
generic records, both as lvalues and rvalues. Without those this feature
will be of comparatively little value, IMNSHO. With them it will be much
more useful and powerful.

cheers

andrew