Before insert trigger to replace new data postgres

Multi tool use
Multi tool use












0















I have a table with two data columns: col1 and col2. Col1 is text field and col2 is time. Col1 is required, col2 is not, so it should have a default value of null. I use pgAdmin, which is completely new to me, as sql trigger is. I have the following trigger function code:



CREATE OR REPLACE FUNCTION schema.table_replace()
RETURNS trigger AS
LANGUAGE 'plpgsql';
$BODY$
BEGIN
(CASE
WHEN NEW.col1='111' THEN NEW.col1='aaa'
WHEN NEW.col1='222' THEN NEW.col1='bbb'
WHEN NEW.col1='333' THEN NEW.col1='ccc'
ELSE NEW.col1='error'
END);
return NEW;
END;
$BODY$


And that could be the before trigger (just current values should be affected, not all rows):



CREATE TRIGGER schema.table_replace
BEFORE INSERT
ON schema.table
EXECUTE PROCEDURE schema.table_replace();


To tell the truth, I know nothing about pgAdmin, it seems to be much more complicated than writing the code and run it with query tool. The problem is to handle the case when there is no second value (it is optional), and in this case the col2 of the row should be left untouched, and the SQL code returns errors as well.
Could you give some help to make it running and creating the function and the trigger?
Thanks.










share|improve this question





























    0















    I have a table with two data columns: col1 and col2. Col1 is text field and col2 is time. Col1 is required, col2 is not, so it should have a default value of null. I use pgAdmin, which is completely new to me, as sql trigger is. I have the following trigger function code:



    CREATE OR REPLACE FUNCTION schema.table_replace()
    RETURNS trigger AS
    LANGUAGE 'plpgsql';
    $BODY$
    BEGIN
    (CASE
    WHEN NEW.col1='111' THEN NEW.col1='aaa'
    WHEN NEW.col1='222' THEN NEW.col1='bbb'
    WHEN NEW.col1='333' THEN NEW.col1='ccc'
    ELSE NEW.col1='error'
    END);
    return NEW;
    END;
    $BODY$


    And that could be the before trigger (just current values should be affected, not all rows):



    CREATE TRIGGER schema.table_replace
    BEFORE INSERT
    ON schema.table
    EXECUTE PROCEDURE schema.table_replace();


    To tell the truth, I know nothing about pgAdmin, it seems to be much more complicated than writing the code and run it with query tool. The problem is to handle the case when there is no second value (it is optional), and in this case the col2 of the row should be left untouched, and the SQL code returns errors as well.
    Could you give some help to make it running and creating the function and the trigger?
    Thanks.










    share|improve this question



























      0












      0








      0








      I have a table with two data columns: col1 and col2. Col1 is text field and col2 is time. Col1 is required, col2 is not, so it should have a default value of null. I use pgAdmin, which is completely new to me, as sql trigger is. I have the following trigger function code:



      CREATE OR REPLACE FUNCTION schema.table_replace()
      RETURNS trigger AS
      LANGUAGE 'plpgsql';
      $BODY$
      BEGIN
      (CASE
      WHEN NEW.col1='111' THEN NEW.col1='aaa'
      WHEN NEW.col1='222' THEN NEW.col1='bbb'
      WHEN NEW.col1='333' THEN NEW.col1='ccc'
      ELSE NEW.col1='error'
      END);
      return NEW;
      END;
      $BODY$


      And that could be the before trigger (just current values should be affected, not all rows):



      CREATE TRIGGER schema.table_replace
      BEFORE INSERT
      ON schema.table
      EXECUTE PROCEDURE schema.table_replace();


      To tell the truth, I know nothing about pgAdmin, it seems to be much more complicated than writing the code and run it with query tool. The problem is to handle the case when there is no second value (it is optional), and in this case the col2 of the row should be left untouched, and the SQL code returns errors as well.
      Could you give some help to make it running and creating the function and the trigger?
      Thanks.










      share|improve this question
















      I have a table with two data columns: col1 and col2. Col1 is text field and col2 is time. Col1 is required, col2 is not, so it should have a default value of null. I use pgAdmin, which is completely new to me, as sql trigger is. I have the following trigger function code:



      CREATE OR REPLACE FUNCTION schema.table_replace()
      RETURNS trigger AS
      LANGUAGE 'plpgsql';
      $BODY$
      BEGIN
      (CASE
      WHEN NEW.col1='111' THEN NEW.col1='aaa'
      WHEN NEW.col1='222' THEN NEW.col1='bbb'
      WHEN NEW.col1='333' THEN NEW.col1='ccc'
      ELSE NEW.col1='error'
      END);
      return NEW;
      END;
      $BODY$


      And that could be the before trigger (just current values should be affected, not all rows):



      CREATE TRIGGER schema.table_replace
      BEFORE INSERT
      ON schema.table
      EXECUTE PROCEDURE schema.table_replace();


      To tell the truth, I know nothing about pgAdmin, it seems to be much more complicated than writing the code and run it with query tool. The problem is to handle the case when there is no second value (it is optional), and in this case the col2 of the row should be left untouched, and the SQL code returns errors as well.
      Could you give some help to make it running and creating the function and the trigger?
      Thanks.







      postgresql plpgsql database-trigger






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Dec 30 '18 at 4:54









      Barbaros Özhan

      12.8k71632




      12.8k71632










      asked Dec 30 '18 at 0:22









      user7113942user7113942

      295




      295
























          2 Answers
          2






          active

          oldest

          votes


















          1














          CASE as a control structure is closed with END CASE (whereas the expression is closed with just an END). And in the branches there are statements, they need to be terminated by a semicolon.



          Your LANGUAGE is also misplaced. That belongs at the end. And you don't need the single quotes.



          You can use an IF to only do the replacement, when col2 is not null.



          CREATE OR REPLACE FUNCTION schema.table_replace()
          RETURNS trigger AS
          $BODY$
          BEGIN
          IF NEW.col2 IS NOT NULL THEN
          CASE
          WHEN NEW.col1 = '111' THEN
          NEW.col1 = 'aaa';
          WHEN NEW.col1 = '222' THEN
          NEW.col1 = 'bbb';
          WHEN NEW.col1 = '333' THEN
          NEW.col1 = 'ccc';
          ELSE
          NEW.col1 = 'error';
          END CASE;
          END IF;

          RETURN NEW;
          END;
          $BODY$
          LANGUAGE plpgsql;


          You also need to declare the trigger as a FOR EACH ROW trigger for NEW to work.



          And a trigger name cannot be schema qualified.



          CREATE TRIGGER table_replace
          BEFORE INSERT
          ON schema.table
          FOR EACH ROW
          EXECUTE PROCEDURE schema.table_replace();





          share|improve this answer


























          • It created the function and trigger I needed. The problem now: if I run the following command I get the error below: INSERT INTO schema.table (col1) VALUES ('111'); ERROR: record "new" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function schema.table_replace() line 3 at CASE SQL state: 55000 Any idea?

            – user7113942
            Dec 30 '18 at 0:52













          • @user7113942: See my edit.

            – sticky bit
            Dec 30 '18 at 1:02



















          0














          The main problem of your example is missing FOR EACH ROW clause in your CREATE TRIGGER statement. Without this clause, the created trigger is a statement trigger with different behave. Your task can be solved with SQL functional CASE statement.



          CREATE OR REPLACE FUNCTION schema.table_replace()
          RETURNS trigger AS
          LANGUAGE 'plpgsql';
          $BODY$
          BEGIN
          IF NEW.col2 NOT NULL THEN
          NEW.col1 := CASE NEW.col1
          WHEN '111' THEN 'aaa'
          WHEN '222' THEN 'bbb'
          WHEN '333' THEN 'ccc'
          ELSE 'error' END CASE;
          END IF;
          RETURN NEW;
          END;
          $BODY$





          share|improve this answer























            Your Answer






            StackExchange.ifUsing("editor", function () {
            StackExchange.using("externalEditor", function () {
            StackExchange.using("snippets", function () {
            StackExchange.snippets.init();
            });
            });
            }, "code-snippets");

            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "1"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53974370%2fbefore-insert-trigger-to-replace-new-data-postgres%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            2 Answers
            2






            active

            oldest

            votes








            2 Answers
            2






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            1














            CASE as a control structure is closed with END CASE (whereas the expression is closed with just an END). And in the branches there are statements, they need to be terminated by a semicolon.



            Your LANGUAGE is also misplaced. That belongs at the end. And you don't need the single quotes.



            You can use an IF to only do the replacement, when col2 is not null.



            CREATE OR REPLACE FUNCTION schema.table_replace()
            RETURNS trigger AS
            $BODY$
            BEGIN
            IF NEW.col2 IS NOT NULL THEN
            CASE
            WHEN NEW.col1 = '111' THEN
            NEW.col1 = 'aaa';
            WHEN NEW.col1 = '222' THEN
            NEW.col1 = 'bbb';
            WHEN NEW.col1 = '333' THEN
            NEW.col1 = 'ccc';
            ELSE
            NEW.col1 = 'error';
            END CASE;
            END IF;

            RETURN NEW;
            END;
            $BODY$
            LANGUAGE plpgsql;


            You also need to declare the trigger as a FOR EACH ROW trigger for NEW to work.



            And a trigger name cannot be schema qualified.



            CREATE TRIGGER table_replace
            BEFORE INSERT
            ON schema.table
            FOR EACH ROW
            EXECUTE PROCEDURE schema.table_replace();





            share|improve this answer


























            • It created the function and trigger I needed. The problem now: if I run the following command I get the error below: INSERT INTO schema.table (col1) VALUES ('111'); ERROR: record "new" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function schema.table_replace() line 3 at CASE SQL state: 55000 Any idea?

              – user7113942
              Dec 30 '18 at 0:52













            • @user7113942: See my edit.

              – sticky bit
              Dec 30 '18 at 1:02
















            1














            CASE as a control structure is closed with END CASE (whereas the expression is closed with just an END). And in the branches there are statements, they need to be terminated by a semicolon.



            Your LANGUAGE is also misplaced. That belongs at the end. And you don't need the single quotes.



            You can use an IF to only do the replacement, when col2 is not null.



            CREATE OR REPLACE FUNCTION schema.table_replace()
            RETURNS trigger AS
            $BODY$
            BEGIN
            IF NEW.col2 IS NOT NULL THEN
            CASE
            WHEN NEW.col1 = '111' THEN
            NEW.col1 = 'aaa';
            WHEN NEW.col1 = '222' THEN
            NEW.col1 = 'bbb';
            WHEN NEW.col1 = '333' THEN
            NEW.col1 = 'ccc';
            ELSE
            NEW.col1 = 'error';
            END CASE;
            END IF;

            RETURN NEW;
            END;
            $BODY$
            LANGUAGE plpgsql;


            You also need to declare the trigger as a FOR EACH ROW trigger for NEW to work.



            And a trigger name cannot be schema qualified.



            CREATE TRIGGER table_replace
            BEFORE INSERT
            ON schema.table
            FOR EACH ROW
            EXECUTE PROCEDURE schema.table_replace();





            share|improve this answer


























            • It created the function and trigger I needed. The problem now: if I run the following command I get the error below: INSERT INTO schema.table (col1) VALUES ('111'); ERROR: record "new" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function schema.table_replace() line 3 at CASE SQL state: 55000 Any idea?

              – user7113942
              Dec 30 '18 at 0:52













            • @user7113942: See my edit.

              – sticky bit
              Dec 30 '18 at 1:02














            1












            1








            1







            CASE as a control structure is closed with END CASE (whereas the expression is closed with just an END). And in the branches there are statements, they need to be terminated by a semicolon.



            Your LANGUAGE is also misplaced. That belongs at the end. And you don't need the single quotes.



            You can use an IF to only do the replacement, when col2 is not null.



            CREATE OR REPLACE FUNCTION schema.table_replace()
            RETURNS trigger AS
            $BODY$
            BEGIN
            IF NEW.col2 IS NOT NULL THEN
            CASE
            WHEN NEW.col1 = '111' THEN
            NEW.col1 = 'aaa';
            WHEN NEW.col1 = '222' THEN
            NEW.col1 = 'bbb';
            WHEN NEW.col1 = '333' THEN
            NEW.col1 = 'ccc';
            ELSE
            NEW.col1 = 'error';
            END CASE;
            END IF;

            RETURN NEW;
            END;
            $BODY$
            LANGUAGE plpgsql;


            You also need to declare the trigger as a FOR EACH ROW trigger for NEW to work.



            And a trigger name cannot be schema qualified.



            CREATE TRIGGER table_replace
            BEFORE INSERT
            ON schema.table
            FOR EACH ROW
            EXECUTE PROCEDURE schema.table_replace();





            share|improve this answer















            CASE as a control structure is closed with END CASE (whereas the expression is closed with just an END). And in the branches there are statements, they need to be terminated by a semicolon.



            Your LANGUAGE is also misplaced. That belongs at the end. And you don't need the single quotes.



            You can use an IF to only do the replacement, when col2 is not null.



            CREATE OR REPLACE FUNCTION schema.table_replace()
            RETURNS trigger AS
            $BODY$
            BEGIN
            IF NEW.col2 IS NOT NULL THEN
            CASE
            WHEN NEW.col1 = '111' THEN
            NEW.col1 = 'aaa';
            WHEN NEW.col1 = '222' THEN
            NEW.col1 = 'bbb';
            WHEN NEW.col1 = '333' THEN
            NEW.col1 = 'ccc';
            ELSE
            NEW.col1 = 'error';
            END CASE;
            END IF;

            RETURN NEW;
            END;
            $BODY$
            LANGUAGE plpgsql;


            You also need to declare the trigger as a FOR EACH ROW trigger for NEW to work.



            And a trigger name cannot be schema qualified.



            CREATE TRIGGER table_replace
            BEFORE INSERT
            ON schema.table
            FOR EACH ROW
            EXECUTE PROCEDURE schema.table_replace();






            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Dec 30 '18 at 1:02

























            answered Dec 30 '18 at 0:43









            sticky bitsticky bit

            14.8k81632




            14.8k81632













            • It created the function and trigger I needed. The problem now: if I run the following command I get the error below: INSERT INTO schema.table (col1) VALUES ('111'); ERROR: record "new" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function schema.table_replace() line 3 at CASE SQL state: 55000 Any idea?

              – user7113942
              Dec 30 '18 at 0:52













            • @user7113942: See my edit.

              – sticky bit
              Dec 30 '18 at 1:02



















            • It created the function and trigger I needed. The problem now: if I run the following command I get the error below: INSERT INTO schema.table (col1) VALUES ('111'); ERROR: record "new" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function schema.table_replace() line 3 at CASE SQL state: 55000 Any idea?

              – user7113942
              Dec 30 '18 at 0:52













            • @user7113942: See my edit.

              – sticky bit
              Dec 30 '18 at 1:02

















            It created the function and trigger I needed. The problem now: if I run the following command I get the error below: INSERT INTO schema.table (col1) VALUES ('111'); ERROR: record "new" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function schema.table_replace() line 3 at CASE SQL state: 55000 Any idea?

            – user7113942
            Dec 30 '18 at 0:52







            It created the function and trigger I needed. The problem now: if I run the following command I get the error below: INSERT INTO schema.table (col1) VALUES ('111'); ERROR: record "new" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function schema.table_replace() line 3 at CASE SQL state: 55000 Any idea?

            – user7113942
            Dec 30 '18 at 0:52















            @user7113942: See my edit.

            – sticky bit
            Dec 30 '18 at 1:02





            @user7113942: See my edit.

            – sticky bit
            Dec 30 '18 at 1:02













            0














            The main problem of your example is missing FOR EACH ROW clause in your CREATE TRIGGER statement. Without this clause, the created trigger is a statement trigger with different behave. Your task can be solved with SQL functional CASE statement.



            CREATE OR REPLACE FUNCTION schema.table_replace()
            RETURNS trigger AS
            LANGUAGE 'plpgsql';
            $BODY$
            BEGIN
            IF NEW.col2 NOT NULL THEN
            NEW.col1 := CASE NEW.col1
            WHEN '111' THEN 'aaa'
            WHEN '222' THEN 'bbb'
            WHEN '333' THEN 'ccc'
            ELSE 'error' END CASE;
            END IF;
            RETURN NEW;
            END;
            $BODY$





            share|improve this answer




























              0














              The main problem of your example is missing FOR EACH ROW clause in your CREATE TRIGGER statement. Without this clause, the created trigger is a statement trigger with different behave. Your task can be solved with SQL functional CASE statement.



              CREATE OR REPLACE FUNCTION schema.table_replace()
              RETURNS trigger AS
              LANGUAGE 'plpgsql';
              $BODY$
              BEGIN
              IF NEW.col2 NOT NULL THEN
              NEW.col1 := CASE NEW.col1
              WHEN '111' THEN 'aaa'
              WHEN '222' THEN 'bbb'
              WHEN '333' THEN 'ccc'
              ELSE 'error' END CASE;
              END IF;
              RETURN NEW;
              END;
              $BODY$





              share|improve this answer


























                0












                0








                0







                The main problem of your example is missing FOR EACH ROW clause in your CREATE TRIGGER statement. Without this clause, the created trigger is a statement trigger with different behave. Your task can be solved with SQL functional CASE statement.



                CREATE OR REPLACE FUNCTION schema.table_replace()
                RETURNS trigger AS
                LANGUAGE 'plpgsql';
                $BODY$
                BEGIN
                IF NEW.col2 NOT NULL THEN
                NEW.col1 := CASE NEW.col1
                WHEN '111' THEN 'aaa'
                WHEN '222' THEN 'bbb'
                WHEN '333' THEN 'ccc'
                ELSE 'error' END CASE;
                END IF;
                RETURN NEW;
                END;
                $BODY$





                share|improve this answer













                The main problem of your example is missing FOR EACH ROW clause in your CREATE TRIGGER statement. Without this clause, the created trigger is a statement trigger with different behave. Your task can be solved with SQL functional CASE statement.



                CREATE OR REPLACE FUNCTION schema.table_replace()
                RETURNS trigger AS
                LANGUAGE 'plpgsql';
                $BODY$
                BEGIN
                IF NEW.col2 NOT NULL THEN
                NEW.col1 := CASE NEW.col1
                WHEN '111' THEN 'aaa'
                WHEN '222' THEN 'bbb'
                WHEN '333' THEN 'ccc'
                ELSE 'error' END CASE;
                END IF;
                RETURN NEW;
                END;
                $BODY$






                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Dec 30 '18 at 14:24









                Pavel StehulePavel Stehule

                22.7k34857




                22.7k34857






























                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Stack Overflow!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53974370%2fbefore-insert-trigger-to-replace-new-data-postgres%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    vpKLqCnqFThonBkkKUz19Jw,0oA8eRIGbl2x6Cs1
                    CC6CltJyVIrh6 3ulTkVzWW C06HnefzTHXxFw4XlFxN,vJF4O2

                    Popular posts from this blog

                    Monofisismo

                    Angular Downloading a file using contenturl with Basic Authentication

                    Olmecas