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, Jul 09 '13 at 04:03
Postgres doesn't have stored procedures, it does support functions though.. — Mike Christensen, Jul 09 '13 at 04: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, Jul 09 '13 at 04: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, Jul 09 '13 at 04:13
This is the [SQL FIDDLE] (sqlfiddle.com/#!12/3e6a7/18) Please solve this query ... @muistooshort — 09Q71AO534, Jul 12 '13 at 06:44

Mike Christensen · Accepted Answer · 2019-01-23T15:21:37.473

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 '19 at 15:21

answered Jul 09 '13 at 04:14

Mike Christensen

88,082
50
208
326

1

@user2561626 - You need to `CREATE` the `FUNCTION` first. Use my example above, I just verified it with Postgres 9.2 on my own server. – Mike Christensen Jul 09 '13 at 04:28
plpgsql does not support autonomous transactions, but other languages do, for example plperl. You can probably accomplish the same using dblink and plproxy. – bma Jul 09 '13 at 04:30
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 – 09Q71AO534 Jul 09 '13 at 04:39
3

@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](http://www.postgresql.org/docs/9.1/static/sql-createfunction.html) – Mike Christensen Jul 09 '13 at 04:45
http://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 – 09Q71AO534 Jul 09 '13 at 04:53
@user2561626 - You've created a function called `MyInsert1` but you're calling `MyInsert` – Mike Christensen Jul 09 '13 at 04:55
thank u mike for clearing even a tiny error prone;-) have a nice day @ mike – 09Q71AO534 Jul 09 '13 at 04:56
This is the [SQL FIDDLE] (sqlfiddle.com/#!12/3e6a7/18) Please solve this query ...@MikeChristensen – 09Q71AO534 Jul 11 '13 at 08:10
1

@lad2025 - Woah sweet! Man I miss working with PG. I'm all MS SQL Server now days, sigh.. – Mike Christensen May 25 '18 at 00:38
Time to update this answer. Stored procedures with transaction control are supported in PG11, which has been released now for several months. – DB140141 Jan 23 '19 at 14:10
@DB140141 Good call! Updated. I haven't used PG in years, I really miss it. – Mike Christensen Jan 23 '19 at 15:24

score 10 · Answer 2 · answered May 24 '18 at 16:27

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.

Nice @lukasz ... How to return the result set i.e tables in stored procedure? — Ajt, Jun 20 '18 at 14:52

score 2 · Answer 3 · answered Jun 09 '14 at 09:41

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

Time to update this answer. Stored procedures with transaction control are supported in PG11, which has been released now for several months. — DB140141, Jan 23 '19 at 14:11

score 1 · Answer 4 · edited Apr 03 '18 at 04:47

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;

How to insert data into table using stored procedures in postgresql

4 Answers4

Linked