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?

I need to insert data ,by creating a funtion or a procedure to insert data into the table app_for_leave — 09Q71AO534
– 09Q71AO534, Commented Jul 9, 2013 at 4:03
Postgres doesn't have stored procedures, it does support functions though.. — Mike Christensen
– Mike Christensen, Commented Jul 9, 2013 at 4:04
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 — 09Q71AO534
– 09Q71AO534, Commented Jul 9, 2013 at 4:06
I'm not sure what you're asking here. You can write a function that does the INSERT I suppose. — mu is too short
– mu is too short, Commented Jul 9, 2013 at 4:13

Mike Christensen · Accepted Answer · 2019-01-23 15:21:37Z

43

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:

inability to return multiple result sets
no support for autonomous transactions (BEGIN, COMMIT and ROLLBACK within a function)
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' );

edited Jan 23, 2019 at 15:21

answered Jul 9, 2013 at 4:14

Mike Christensen

91.8k52 gold badges223 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Mike Christensen Over a year ago

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

bma Over a year ago

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

09Q71AO534 Over a year ago

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

Mike Christensen Over a year ago

@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

Mike Christensen Over a year ago

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

|

Lukasz Szozda · Accepted Answer · 2018-05-24 16:27:32Z

11

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.

answered May 24, 2018 at 16:27

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

2 Comments

Lajith Over a year ago

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

oligofren Over a year ago

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

09Q71A0548 · Accepted Answer · 2014-06-09 09:41:29Z

2

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

answered Jun 9, 2014 at 9:41

09Q71A0548

865 bronze badges

1 Comment

DB140141 Over a year ago

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

MJM · Accepted Answer · 2018-04-03 04:47:55Z

1

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;

edited Apr 3, 2018 at 4:47

MJM

5,3315 gold badges32 silver badges56 bronze badges

answered Apr 3, 2018 at 4:02

Yerbol

113 bronze badges

Collectives™ on Stack Overflow

How to insert data into table using stored procedures in postgresql

4 Answers 4

11 Comments

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

11 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related