Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2007
    Posts
    4

    Unanswered: columns in NEW or OLD

    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

  2. #2
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    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!
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  3. #3
    Join Date
    Dec 2007
    Posts
    4
    Quote 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

  4. #4
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    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
    Vi veri veniversum vivus vici
    By the power of truth, I, a living man, have conquered the universe

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Dec 2007
    Posts
    4
    Quote 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

  7. #7
    Join Date
    Dec 2007
    Posts
    4
    Quote 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

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •