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?

Barolet

I need to insert data ,by creating a funtion or a procedure to insert data into the table app_for_leave – Barolet

Postgres doesn't have stored procedures, it does support functions though.. – Merger

so there is no other way to insert data except the DML language queries INSERT statement..or is there any other way to solve this @mike christensen – Barolet

Sure, use a function.. – Merger

I'm not sure what you're asking here. You can write a function that does the INSERT I suppose. – Ploch

This is the [SQL FIDDLE] (sqlfiddle.com/#!12/3e6a7/18) Please solve this query ... @muistooshort – Barolet

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

Merger

@user2561626 - You need to CREATE the FUNCTION first. Use my example above, I just verified it with Postgres 9.2 on my own server. – Merger

plpgsql does not support autonomous transactions, but other languages do, for example plperl. You can probably accomplish the same using dblink and plproxy. – Demoss

what does the LANGUAGE 'plpsql' 'VOLATILE' and 'cost 100' mean in the query.. is it the name of the language or something which states different @mike – Barolet

@user2561626 - Postgres supports various languages, such as PL/pgSQL, Perl, C, there's even a JavaScript extension. VOLATILE indicates the function must be run each time and the result cannot be cached (only VOLATILE functions may change the database). This is the default so my code is actually redundant. The COST provides an estimated execution cost for the planner. More Details – Merger

sqlfiddle.com/#!12/63d9a/1 i just tried the above by keeping a serial datatype,and a name into the function ... then it is showing few notices.. can u clear that for me @mike – Barolet

@user2561626 - You've created a function called MyInsert1 but you're calling MyInsert – Merger

thank u mike for clearing even a tiny error prone;-) have a nice day @ mike – Barolet

This is the [SQL FIDDLE] (sqlfiddle.com/#!12/3e6a7/18) Please solve this query ...@MikeChristensen – Barolet

@lad2025 - Woah sweet! Man I miss working with PG. I'm all MS SQL Server now days, sigh.. – Merger

Time to update this answer. Stored procedures with transaction control are supported in PG11, which has been released now for several months. – Cannibal

@Cannibal Good call! Updated. I haven't used PG in years, I really miss it. – Merger

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.

Crotchety

Nice @lukasz ... How to return the result set i.e tables in stored procedure? – Chronic

@Atj You use a FUNCTION, not a stored PROCEDURE. – Yungyunick

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

Bertero

Time to update this answer. Stored procedures with transaction control are supported in PG11, which has been released now for several months. – Cannibal

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;

Intendancy