Tom Lane wrote:
> "Florian G. Pflug" <fgp@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.
>
> 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
> of the principal reasons why we work so hard to support multiple PLs
> is that they have different strengths. If you need something that's
> more dynamically typed than plpgsql, you should go use something
> else.
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.
>
> 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
> should not need a hard-wired list of which types those are.
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
