Re: Allow SQL/plpgsql functions to accept record - Mailing list pgsql-hackers

From Andrew Dunstan
Subject Re: Allow SQL/plpgsql functions to accept record
Date
Msg-id 553EF929.6080903@dunslane.net
Whole thread Raw
In response to Re: Allow SQL/plpgsql functions to accept record  (Jim Nasby <Jim.Nasby@BlueTreble.com>)
Responses Re: Allow SQL/plpgsql functions to accept record
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: Jim Nasby
Date:
Subject: Re: Temporal extensions
Next
From: Heikki Linnakangas
Date:
Subject: Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0