Re: Allow SQL/plpgsql functions to accept record

On 04/22/2015 11:29 AM, Jim Nasby wrote:
> On 4/20/15 2:04 PM, David G. Johnston wrote:
>>
>> ​SELECT (src.v).* FROM ( VALUES (ROW(1,2,3)) ) src (v)​;
>> ERROR: record type has not been registered
>>
>> While it may not be necessary to solve both problems I suspect they have
>> the same underlying root cause - specifically the separation of concerns
>> between the planner and the executor.
>
> I don't think they're related at all. C functions have the ability to
> accept a record, so the executor must be able to support it. It's just
> that SQL and plpgsql functions don't have that support. I suspect
> that's just because no one has gotten around to it.

Well, that's assuming everyone else thinks it would be a good idea.
Maybe they do, but I wouldn't assume it.

The answer in the past has been to use more dynamically typed languages
such as perl for which this problem is well suited.

There are actually several problems: first, given an arbitrary record
plpgsql has no easy and efficient way of finding out what field names it
has. Second, even if it has such knowledge it has no way of using it -
it's not like JavaScript where you can use a text value as a field name.
And third, it has no way of creating variables of the right type to hold
extracted values.

All of these could possibly be overcome, but it would not be a small
piece of work, I suspect. Given that plperl buys you all of that
already (just try this, for example) people might think it not worth
the extra trouble.

create function rkeys(record) returns text[] language plperl as $$
my $rec = shift; return [ keys %$rec ]; $$;
select unnest(rkeys(r)) from (select * from pg_class limit 1) r;

cheers

andrew