plsql_function_insert1.htm
Objectives :
PL/pgSQL :
  • Creating function, insert values and display output.
  • Updating single column:
    UPDATE TABLE-X SET column_name = new_values where column_name = old_value
  • Updating many columns in TABLE-1 from another TABLE-2
    UPDATE table1 SET (new_value1, new_value2) =
    (SELECT old_value1, old_value2 FROM table2
    WHERE table2.eid = table1.eid);

A screen shot showing the list of tables before adding the new table.
A  table , named "emp2", was created as shown below.
Creating and compiling a function :

CREATE OR REPLACE FUNCTION MyInsert(_eid integer,_fname text,
_lname text,_job text, _deptno integer,_hired date,
_released date, _salary real, _comm real)
RETURNS void AS
$BODY$
BEGIN
INSERT INTO emp2( eid,fname,lname,job,deptno,hired,
released, salary, comm)
VALUES(_eid,_fname,_lname,_job, _deptno, _hired,
_released,_salary, _comm);
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
 SQL-Scripts to insert values in the table named "emp2":
pgsdemo1=#

select * from MyInsert(1,'John','Doe','Manager',20,'1995-04-14','2013-04-04',30000,1500);

Querying the table "emp2" :

Querying the table "emp2", using PHP script  over a web-browser:

Inserting data using PHP Query Tool:

select * from MyInsert(2,'Peter','Pan','Manger',20,'2013-01-24','2019-01-04',30000,1500)
Now Update Salary

UPDATE EMP2 SET salary = 95000 where salary = 30000