How to execute an insert or update operation in postgres 10 ( that not have store procedures)?-CodePudding

it is possible to execute an insert or update operation using only functions in postgres?. I am using version 10.14 that not support store procedures.

CodePudding user response：

You should use INSERT ... ON CONFLICT. It doesn't matter much if you run that from a function or not.

CodePudding user response：

Store data in a table using function

-- postgreSQL
CREATE OR REPLACE FUNCTION save_testsp(p_roll_no character varying
    , p_role character varying, p_address_type character varying, p_address_value character varying)
  RETURNS void AS
$BODY$
      BEGIN
        INSERT INTO test(roll_no, role, address_type, address_value)
        VALUES(p_roll_no, p_role, p_address_type, p_address_value);
      END;
  $BODY$
  LANGUAGE plpgsql VOLATILE;

Execute a function by using below command

SELECT * FROM save_testsp('1', 'A', 'advs', 'adfg');

Please check this url https://dbfiddle.uk/?rdbms=postgres_10&fiddle=c6307d115b4e5cfc7462dd8c265b1115