proposal - operators ? and ->> for type record, and functions record_keys and record_each_text

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: proposal - operators ? and ->> for type record, and functions record_keys and record_each_text
Date: 2021-03-08 21:29:47
Message-ID: CAFj8pRA1wYfHcNgRzc3D4ysB=JwfB4hiPeF+ERQ5yjkr2gKGcg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi

When I wrote an reply to questing

https://stackoverflow.com/questions/66523737/postgresql-10-pl-pgsql-test-if-column-exits-in-a-record-variable

I found an interesting idea to have some basic functions and operators for
record type (similar to json, jsonb or hstore).

Now we can do almost all tasks on record type by cast to jsonb type. But
this transformation has some overhead (and for some tasks is not
necessary), and it is not too intuitive too.

I don't think so we need full functionality like hstore or jsonb (minimally
because record type cannot be persistent and indexed), but some basic
functionality can be useful.

-- tests of basic helper functions for record type
do $$
declare
r record;
k text; v text; t text;
begin
select oid, relname, relnamespace, reltype from pg_class limit 1 into r;
if not r ? 'xxx' then
raise notice 'pg_class has not column xxx';
end if;

if r ? 'relname' then
raise notice 'pg_class has column relname';
end if;

foreach k in array record_keys_array(r)
loop
raise notice '% => %', k, r->>k;
end loop;

raise notice '---';

-- second (slower) variant
for k in select * from record_keys(r)
loop
raise notice '% => %', k, r->>k;
end loop;

raise notice '---';

-- complete unpacking
for k, v, t in select * from record_each_text(r)
loop
raise notice '% => %(%)', k, v, t;
end loop;
end;
$$;

What do you think about this proposal?

Comments, notes?

Regards

Pavel

Attachment Content-Type Size
basic-record-type-funcs-and-operators.patch text/x-patch 14.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2021-03-08 21:38:55 Re: Removing vacuum_cleanup_index_scale_factor
Previous Message Robert Haas 2021-03-08 21:14:59 Re: [HACKERS] Custom compression methods