Re: [HACKERS] Inspection of row types in pl/pgsql and pl/sql

[Florian G. Pflug]

Tom Lane wrote:

"Florian G. Pflug" <f...@phlo.org> writes:

While I agree that handling arbitrary datatypes at runtime would be
 nice, I really don't see how that could ever be done from within a
 plpgsql procedure, unless plpgsql somehow morphs into a
dynamically typed language.

of the principal reasons why we work so hard to support multiple PLs

else.

Which is not likely to happen, which is why this is fundamentally a dead end. I don't think it's appropriate to put ugly, hard to use band-aids over the fact that plpgsql isn't designed to do this. One is that they have different strengths. If you need something that's more dynamically typed than plpgsql, you should go use something

In principle, I agree. In pratice, however, the company who I do my
current project for has settled on plpgsql and isn't willing to use
other PLs in their software because they lack the skill to maintain code
written in other PLs. Therefore I'm trying to find an at least somewhat
acceptable solution using plpgsql.

Plus, fully generic handling of data of arbitrary type is a
somewhat strange notion anyway, because it leaves you with very few
operations guaranteed to be defined for those values. In the case
of PG, you'd be pretty much limited to casting those values from
and to text.

should not need a hard-wired list of which types those are.

Well, that's the wrong way to look at it. To me, the right design would involve saying that my trigger needs to do operation X on the data, and therefore it should support all datatypes that can do X. It

True, but that'd require fairly large changes to plpgsql AFAICS.

Perhaps it would help if we looked at some specific use-cases that people need, rather than debating abstractly. What do you need your generic trigger to *do*?

I need to build a global index table of all values of a certain type
together with a pointer to the row and table that contains them. Since
all involved tables have an "id" column, storing that pointer is the
easy part. The hard part is collecting all those values in an
insert/update/delete trigger so that I can update the global index
accordingly.

Currently, a set of plpgsql functions generate a seperate trigger
function for each table. Yuck!

Instead of this nearly-impossible to read code-generating function I
want to create a generic trigger function that works for any of the
involved tables. Preferrably in plpgsql because of the skill issue
mentioned above.

best regards,
Florian Pflug

smime.p7s
Description: S/MIME Cryptographic Signature