Thread: Insert problem

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    4
    Rep Power
    0

    Insert problem


    I'm new at PLPGSQL (but experienced at PL/SQL) trying to write a plpgsql function to insert lines into a table. I'm making some mistake but can't find it. I'm trying to compile on a version of Postgres called XC. Info can be found at Sourceforge.

    Table:

    labba_int (msg integer)

    Function:

    CREATE OR REPLACE
    FUNCTION test_tmp() RETURNS VARCHAR AS $$
    BEGIN
    INSERT INTO labba_int (MSG) VALUES(2);
    RETURN '0';
    EXCEPTION
    WHEN OTHERS THEN RETURN SQLSTATE;
    END;
    $$ LANGUAGE PLPGSQL;

    Running the function:

    select test_tmp();

    Returned Error:

    0A000

    Nothing in the table.

    I can insert via ordinary SQL, but the db won't let med insert stuff with a function. I've tried inserting texts as well, but with no success. It's important since I'm trying to migrate stored procedures from MySQL.

    Plz advice.

    Edit: The installation I'm running on is an XC. Formatted on request.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    Originally Posted by Johan IV
    I can insert via ordinary SQL, but the db won't let med insert stuff with a function. I've tried inserting texts as well, but with no success.
    Works for me: http://sqlfiddle.com/#!12/8e5ed/1

    Edit: The installation I'm running on is an XC.
    I have no idea what "XC" is supposed to me.

    And please, the next time properly format your code.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    4
    Rep Power
    0
    XC stands for eXtensible Cluster.

    You can find a wiki on Sourceforge.

    The code was formatted prior to pasting, I'll fix it now.

    Tx for replying.

    Originally Posted by shammat
    Works for me:

    I have no idea what "XC" is supposed to me.

    And please, the next time properly format your code.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    4
    Rep Power
    0
    It turns out that XC in my version does not support DML in PLPGSQL-functions. But you can write DML in SQL-functions.

    Tx for looking.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    Originally Posted by Johan IV
    The code was formatted prior to pasting, I'll fix it now.
    You should use [code] tags for that.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by shammat
    You should use [code] tags for that.
    Thanks again.

IMN logo majestic logo threadwatch logo seochat tools logo