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' ); 

Answer 2

Starting from PostgreSQL 11 you could create stored procedures and invoke them using CALL:

CREATE PROCEDURE MyInsert(_sno integer, _eid integer, _sd date,                           _ed date, _sid integer, _status boolean) LANGUAGE SQL AS $$     INSERT INTO app_for_leave(sno, eid, sd, ed, sid, status)     VALUES(_sno, _eid, _sd, _ed, _sid, _status);    $$;  CALL MyInsert(1,101,'2013-04-04','2013-04-04',2,'f' ); 

Plus it allows to handle transaction

SQL Stored Procedures

PostgreSQL 11 introduces SQL stored procedures that allow users to use embedded transactions (i.e. BEGIN, COMMIT/ROLLBACK) within a procedure. Procedures can be created using the CREATE PROCEDURE command and executed using the CALL command.

Answer 3

PostgreSQL doesn't support stored procedures, but you can get the same result using a function.

Whatever the data you want to insert in to the table are given as parameters to the function you are creating.

CREATE OR REPLACE represents if a function with the same name (you are using) is already present in the database, then it will be replaced or else if no function with the same name is not present then a new function will be created.

You have to write the insesrtion query inside the body of the function.

CREATE OR REPLACE FUNCTION Insert_into_table(_sno INTEGER, _eid INTEGER, _ename VARCHAR(20), _sd DATE, _ed DATE, _sid INTEGER)
      RETURNS void AS
      $BODY$
          BEGIN
            INSERT INTO app_for_leave(sno, eid, sd, ed, sid)
            VALUES(_sno, _eid, _sd, _ed, _sid);
          END;
      $BODY$
      LANGUAGE 'plpgsql' VOLATILE
      COST 100;

As you have already mentioned in the table a default value for the column Status, now it is no need to insert data into that column

Here is the SQLFiddle Link for your understanding

Answer 4

CREATE OR REPLACE FUNCTION  new_bolshek(parent_id bigint, _key text, _value text, enabled boolean)
  RETURNS SETOF bolshekter AS
  $BODY$
  DECLARE
    new_id integer;
    returnrec bolshekter;
  BEGIN
        INSERT INTO bolshekter(parent_id, content_key, content_value, enabled)
        VALUES(parent_id, _key, _value, enabled) RETURNING id INTO new_id;
        FOR returnrec IN SELECT * FROM bolshekter where id=new_id LOOP
            RETURN NEXT returnrec;
        END LOOP;
  END;
  $BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;