sql - How to insert data into table using stored procedures in postgresql

Question

CREATE TABLE app_for_leave
(
  sno integer NOT NULL,
  eid integer,
  ename varchar(20),
  sd date,
  ed date,
  sid integer,
  status boolean DEFAULT false,
  CONSTRAINT pk_snoa PRIMARY KEY (sno)
);

Basic Insertion is ::

INSERT INTO app_for_leave(sno, eid, sd, ed, sid, status)
 VALUES(1,101,'2013-04-04','2013-04-04',2,'f' );

...

INSERT INTO app_for_leave(sno, eid, sd, ed, sid, status) VALUES (?, ?, ?, ?, ?, ?);

My Requirement:: How to insert data into a table using stored procedures?

Answer 1

PostgreSQL didn't support stored procedures until PG11. Prior to that, you could get the same result using a function. For example:

CREATE FUNCTION MyInsert(_sno integer, _eid integer, _sd date, _ed date, _sid integer, _status boolean)
  RETURNS void AS
  $BODY$
      BEGIN
        INSERT INTO app_for_leave(sno, eid, sd, ed, sid, status)
        VALUES(_sno, _eid, _sd, _ed, _sid, _status);
      END;
  $BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

You can then call it like so:

select * from MyInsert(1,101,'2013-04-04','2013-04-04',2,'f' );

The main limitations on Pg's stored functions - as compared to true stored procedures - are:

1. inability to return multiple result sets
2. no support for autonomous transactions (BEGIN, COMMIT and ROLLBACK within a function)
3. no support for the SQL-standard CALL syntax, though the ODBC and JDBC drivers will translate calls for you.

Example

Starting from PG11, the CREATE PROCEDURE syntax is introduced which provides support for transactions.

CREATE PROCEDURE MyInsert(_sno integer, _eid integer, _sd date, _ed date, _sid integer, _status boolean)
LANGUAGE SQL
AS $BODY$
    INSERT INTO app_for_leave(sno, eid, sd, ed, sid, status)
    VALUES(_sno, _eid, _sd, _ed, _sid, _status);   
$BODY$;

Which could be called with:

CALL MyInsert(1,101,'2013-04-04','2013-04-04',2,'f' );