Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2014
    Posts
    8

    Unanswered: plpgsql - transactions

    Hi - both new to the forums and new to PostgreSQL coming from a MSSQL background.

    So, first - Hi!

    Second. I always used stored procedures to house my business logic and plan to continue that with my PostgreSQL 9.3 installation and plpgsql functions.

    So far so good but have run into an issue I hope someone can help with.

    I have a function that writes information to a table then calls a second function to send an email based on this newly created row. This second is a generic function used by quite a few others. Problem is that the email function can't see data written to the tables from the first function.

    I'm pretty sure that it's a transaction issue but can't seem to find a way around this. I would have thought that any functions called within other functions would be part of the same transaction and see data accordingly but this doesn't seem to be the case.

    Any one have any ideas what I'm missing.

    Hope I've explained this ok! Any and all help greatly appreciated.

    Many Thanks
    Pat

  2. #2
    Join Date
    Feb 2014
    Posts
    8
    Just done some more testing and looks like I'm right.

    Basically any data written to a table is not available to any functions called after that INSERT/UPDATE even though they are still part of the same transaction.

    Whereas, any data written to a table is written to a table is available to the following SQL statements in the same transaction so long as it's within the same function.

    Any ideas what I'm missing here?

  3. #3
    Join Date
    Feb 2014
    Posts
    8
    Turns out this was completely user error...

    I had a malformed variable assignment statement that was blowing the whole thing out of the water.

    (See my thread on multiple variable assignment for details)

    Sorry if I caused anyone any confusion

    Pat

Posting Permissions

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