[scoast]

G'day

Trying to write a trigger to update other tables based on data inserted, updated or deleted.

I can get the operation from TG_OP, the table name from TG_RELNAME, and I can get the row from OLD or NEW as applicable. What I can't get is the columns. I don't particularly want to hard code the column names and have to write functions for each individual table as there are going to be quite a few, and the tables are probably going to evolve as well.

Hoping there is some function that I've missed that allows reference to columns either by name or number?

Can't believe I'm the first to ask this - I did search, but got no results at all.


Regards

[amthomas]

hmmm.. I don't know what problem you are trying to solve, but dynamicly fetching column names doesn't seem like a good idea. These triggers should be working with tables that are specific entities with their own specific data. You should probably explicitly deal with the column names when doing a function. Trying to deal with multiple tables as if they were the same seems like it would lead to a lot of 'weirdness.'

I do not know how to dynamically get a column name. I haven't written many functions but I am not sure if that is possible. I just looked through the trigger procedures for PL/pgSQL and didn't see any reference to what you want in the list of special variables.

good luck!

[scoast]

Originally Posted by amthomas

hmmm.. I don't know what problem you are trying to solve, but dynamicly fetching column names doesn't seem like a good idea. These triggers should be working with tables that are specific entities with their own specific data. You should probably explicitly deal with the column names when doing a function. Trying to deal with multiple tables as if they were the same seems like it would lead to a lot of 'weirdness.'

I do not know how to dynamically get a column name. I haven't written many functions but I am not sure if that is possible. I just looked through the trigger procedures for PL/pgSQL and didn't see any reference to what you want in the list of special variables.

good luck!

Thanks for your reply, amthomas.

I don't think trying to make a generic trigger function is 'weird'... less code to maintain = less errors.

I did look myself, and have googled around a bit, I was hoping there might have been some 3rd party function written by a pg guru :0), but I can't find any references either.

What I am trying to do is:

a 'master' db (postgres) on a hosted server.
users (only two at this stage) will be inserting and updating data. There will also be python scripts gathering data from various websites, extracting data and updating the master db.
a few 'client' db's (probably firebird) which have only some of the tables of the master, but the data in these tables should be kept in sync with the master (one way only - a client only gets updates).

My solution was to use triggers to maintain a few additional tables which hold enough data to generate the sql statements needed to update the clients. A python script (daily cron job) will run queries on these tables to generate the sql, write it to a file, zip it up as a sequentially numbered file and email it to the clients. Sounds easy enough, but I'm stumped without the column names...


Regards

[amthomas]

sorry.. shouldn't have said weird. that has a bad connotation.

I am curious though what tables you would have that you want to preform the exact same operations on.

TableA
a int
b varchar
c date

TableB
aa varchar
bc date
dd varchar

you don't manipulate TableA just like TableB. triggers are for doing something with a specific table and there should be no problem using the column names for that reason. If you have a bunch of identical tables out there with the same information then perhaps they should be 1 table?

I know this isn't answering your question, but I am curious about your problem and maybe I can learn something from it

If you use PL/Python then perhaps you could pull the schema out of the db somehow and look up the column names dynamically for yourself. I don't know how to do this, but it is a thought

[loquin]

Also, replication exists for postgreSQL (Slony)

I'm curious as to why you're considering a mixed system (postgresql/firebird) since you can backup a table to/from a script file using pgSQL (COPY,) whereas in a mixed system, you would probably have to do a LOT more customization to adjust the SQL for the different dialects...


BTW. Column data (name, type, length, precision, etc.) are available in the Information_Schema.

[scoast]

Originally Posted by loquin

Also, replication exists for postgreSQL (Slony)

I'm curious as to why you're considering a mixed system (postgresql/firebird) since you can backup a table to/from a script file using pgSQL (COPY,) whereas in a mixed system, you would probably have to do a LOT more customization to adjust the SQL for the different dialects...


BTW. Column data (name, type, length, precision, etc.) are available in the Information_Schema.

G'day,

The SQL I would be generating would be simple INSERT/UPDATE/DELETE statements. No problem there.

I don't want to backup tables, I want to replicate changes made to the tables. The total size of the client db would be around the 700 meg mark.

Well, I've had a bit of a sleep on it and decided what I might do is invest a bit more time and write a python script that generates a custom trigger function for each table. This should work well (hopefully), and anyroad, it'll be a learning experience.

Thanks for your help


Regards

[scoast]

Originally Posted by amthomas

sorry.. shouldn't have said weird. that has a bad connotation.

I am curious though what tables you would have that you want to preform the exact same operations on.

TableA
a int
b varchar
c date

TableB
aa varchar
bc date
dd varchar

you don't manipulate TableA just like TableB. triggers are for doing something with a specific table and there should be no problem using the column names for that reason. If you have a bunch of identical tables out there with the same information then perhaps they should be 1 table?

I know this isn't answering your question, but I am curious about your problem and maybe I can learn something from it

If you use PL/Python then perhaps you could pull the schema out of the db somehow and look up the column names dynamically for yourself. I don't know how to do this, but it is a thought

G'day,

No, the tables are completely normalised. The tables are different, the operation that I want to execute with a trigger is the same (with different data - ie the table name and column names)

I'll probably go down the road of 1 table, 1 function - But I will try to automate it.

I don't think the hosting service has PL/Python...

Regards

[loquin]

I want to replicate changes made to the tables.

That's what slony does.

Now, I haven't set up replication with Slony, so I don't know if it offers the granularity to replicate data changes in partial databases (tables X, Y, and Z, for instance.) If it does, that might be the best bet.



Again, as far as column metadata - look up the Information_Schema in the postgreSQL documentation.