Inspection of row types in pl/pgsql and pl/sql

From: "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>
To: Postgresql-Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Inspection of row types in pl/pgsql and pl/sql
Date: 2009-11-13 17:27:59
Message-ID: 4AFD971F.9070008@phlo.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi

I'm currently working on a project where we need to build a global cache
table containing all values of certain types found in any of the other
tables. Currently, a seperate insert, update and delete (plpgsql)
trigger function exists for each table in the database which is
auto-generated by a (plpgsql) function which queries the system catalogs
to find all fields with a certain type, and then generates the
appropriate plpgsql function using EXECUTE '...'.

I'd like to replace this function-generating function by a generic
trigger function that works for all tables. Due to the lack of any way
to inspect the *structure* of a record type, however, I'd have to use a
C language function for that, which induces quite some maintenance
headaches (especially if deployed on windows).

I'm therefore thinking about implementing the following generate-purpose
inspection functions for row types

record_length(record) returns smallint
Returns the number of fields in the given record.

record_names(record) returns name[]
Returns the names of the record's fields. Array will contain NULLs
if one or more fields are unnamed.

record_types(record) returns regtype[];
Returns the OIDs of the record's types. Array won't contain NULLs

record_value(record, name, anyelement) returns anyelement
Returns the value of a certain (named) field. The type of the third
argument defines the return type (its value is ignored). The
field's value is cast to that type if possible, otherwise an
error is raised.

record_value(record, smallint, anyelement) returns anyelement
Returns the value of the field at the given position.

record_values(record, regtype, anyelement) returns anyarray
Returns an array of all values of all fields with the given type or
whose type is a domain over the given type. No casting is done.

Any comment/critique is appreciated.

Would anyone else find those functions useful?

best regards,
Florian Pflug

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Brendan Jurd 2009-11-13 17:32:05 Re: next CommitFest
Previous Message Bruce Momjian 2009-11-13 17:25:23 Re: next CommitFest