postgres sql function with truncate and insert

0
votes

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?

2
postgresql

2 Answers

1
votes

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;
0
votes

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'