postgres sql function with truncate and insert

Question

I created a postgres sql function which perform truncate and then insert rows into table. Below is the function

CREATE OR REPLACE FUNCTION reset_Default()        
RETURNS VOID AS '
BEGIN
        TRUNCATE TABLE details;
        INSERT INTO details values('car',2);
       INSERT INTO details values('bus',4);


        RETURN;
END;    
' LANGUAGE 'plpgsql';

But im getting below errors

ERROR: syntax error at or near "car" LINE 6:VALUES('car',2 ^ CREATE FUNCTION

ERROR: cannot change return type of existing function

HINT: Use DROP FUNCTION first.

Can I know the reason?

Gurmokh · Accepted Answer · 2017-06-21 16:01:46Z

1

Why are you single quoting the body of your function. While this might work it can cause issues like you are experiencing. As wildplasser states use dollar signs. Then your function will compile fine.

eg

CREATE OR REPLACE FUNCTION reset_Default()        
RETURNS VOID AS $$
BEGIN
    TRUNCATE TABLE details;
    INSERT INTO details values('car',2);
   INSERT INTO details values('bus',4);
RETURN;
END;    
$$ LANGUAGE plpgsql;

answered Jun 21, 2017 at 16:01

Gurmokh

2,09127 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cignitor Over a year ago

once function is created, can we run a scheduler in postgre that execute the reset_Default() periodically?

Gurmokh Over a year ago

checkout pg_cron

Gordon Linoff · Accepted Answer · 2017-06-21 12:22:12Z

0

You need to double up single quotes in a string. So:

CREATE OR REPLACE FUNCTION reset_Default()        
RETURNS VOID AS '
BEGIN
    TRUNCATE TABLE details;
    INSERT INTO details(col1, col2) values(''car'',2);  -- use actual column names
    INSERT INTO details(col1, col2) values(''bus'',4);

    RETURN;
END;    
' LANGUAGE 'plpgsql'

answered Jun 21, 2017 at 12:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

wildplasser Over a year ago

Or use dollar-quoting for the function body.

Collectives™ on Stack Overflow

postgres sql function with truncate and insert

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related