On 7/31/06, Worky Workerson <[EMAIL PROTECTED]> wrote:
I'm trying to trigger a whole bunch of partitions at once (initial DB
setup) using the same plpgsql trigger. The trigger is basically the
merge trigger in the docs (i.e. UPDATE IF NOT FOUND RETURN NEW ...).
I need to use the TG_RELNAME variable within the "UPDATE" in the
trigger so that I can use the same function to trigger all of the
partitions (correct?), the problem is that I can't quite figure out
how. I figure that I will have to use EXECUTE on a string that I
build up, right? The problem that I'm having with this approach is
that some of the columns of NEW don't have a text conversion, and I'm
getting an error whenever the trigger fires. Is there a way around
this and/or a better way to trigger a bunch of partitions with the
same function?
I don't think it's possible. however, what is possible and achieves
roughly the same affect is to query the system catalogs (or
information schema) and via dynamic sql cut trigger
funtions/procedures by looping the results of your query. non-dynamic
sql will usually be a bit faster than dynamic as a bonus, the only
downsie is you are creating a lot of functions, albeit in easy to
manage fashion. If you are really clever, you can put your trigger
functions in a special schema for organizational purposes.
to do this the 'functional' way:
create or replace function create_trigger_for_table(table_name text,
schema_name text) returns void as
$$
begin
excecute 'create or replace function ' -- and so forth
end;
$$;
and to invoke the function:
select create_trigger_for_table(table_name , schema_name ) from
information_schema.tables -- and so forth
regards,
merlin
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
