How to Insert Data into Table Using Stored Procedures in Postgresql

How to insert data into table using stored procedures in postgresql

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

How to insert data from stored procedure into a new table using postgresql

Procedures cannot be used in SQL. If you must have a stored procedure (define as code stored in the database to be executed) then change to a SQL function. Which returns a type.

create function copy_customer_list()
  returns setof customer_list 
  language sql
as $$
   Select * from customer_list; 
$$;  

Then you can insert into other table with

insert into customer  
  select * from copy_customer_list();

Trying to create a stored procedure to insert data into postresql database - how to automatically enter primary key?

you need to specify the column list with the INSERT statement:

INSERT INTO testing_serial (name, age) VALUES ("name","age");

Stored procedure to insert data into Postgresql

I don't understand the error message since it's not in English, but I can see a few problems in your code

CREATE OR REPLACE FUNCTION insert_val(IN val int) RETURNS VOID  AS 
$body$
    BEGIN
      FOR i IN 1..10 LOOP
        insert into test (val)
        values($23);
      END LOOP;
   END;
   $body$ Language 'plpgsql' VOLATILE;

You were missing the return type, you were missing AS and you have forgotten to name the in argument.

Create stored procedure. Insert data from one table into another. Execute every new entry

I believe this is what you're after. We don't have true DDL, so if there are any column names that need correcting, you'll need to fix those:

CREATE TRIGGER DuplicateInsert ON dbo.Reviewed_Renewal_Policy 
AFTER INSERT AS BEGIN

    INSERT INTO dbo.tblLogPolicyRenewalNotes(Id, UniqClient, Client, DateCreated, InsertedBy)
    SELECT Id, UniqClient, Client, DateCreated, InsertedBy
    FROM inserted;

END;

Obviously you can rename the trigger to something else.

PostgreSQL- If data in table, delete for stored procedure

If you always want to delete all data from table then just do always

truncate erp.tb_quarter;

place it in next line after BEGIN

If you want to delete only data from calculated range do delete with proper where

Additionaly in place of loop you can just use INSERT INTO ... SELECT FROM construction

Example

---

Related Topics