PostgreSQL/Postgre SQL/Insert Data

Insert data to a log table

   <source lang="sql">

postgres=# postgres=# create table logtable(t varchar(10), value varchar(10)); CREATE TABLE postgres=# postgres=# postgres=# CREATE FUNCTION logfunc1(logtxt text) RETURNS timestamp AS $$ postgres$# BEGIN postgres$# INSERT INTO logtable VALUES (logtxt, "now"); postgres$# RETURN "now"; postgres$# END; postgres$# $$ LANGUAGE plpgsql; CREATE FUNCTION postgres=# postgres=# select logfunc1("text");

         REATE
       logfunc1

---

2006-10-21 15:16:38.375

(1 row) postgres=# postgres=# select * from logtable;

   REATE
 t   | value

---

+-------

text | now

(1 row) postgres=# postgres=# drop function logfunc1(logtxt text); DROP FUNCTION postgres=# drop table logtable; DROP TABLE postgres=# postgres=#

      </source>
   
  

Using function to insert data to a table

   <source lang="sql">

postgres=# postgres=# postgres=# CREATE TABLE "shipments" ( postgres(# "id" integer NOT NULL, postgres(# "customer_id" integer, postgres(# "isbn" text, postgres(# "ship_date" timestamp with time zone postgres(# ); CREATE TABLE postgres=# insert into shipments values (2, 107, "0394800753","2001-09-22 20:18:56-07"); INSERT 0 1 postgres=# postgres=# select * from shipments;

id | customer_id |    isbn    |       ship_date

---

+-------------+------------+------------------------

 2 |         107 | 0394800753 | 2001-09-22 20:18:56-07

(1 row) postgres=# postgres=# drop function add_shipment(integer,text); DROP FUNCTION postgres=# postgres=# CREATE FUNCTION "add_shipment" (integer,text) RETURNS timestamp with time zone AS " postgres"# DECLARE postgres"# customer_id ALIAS FOR $1; postgres"# isbn ALIAS FOR $2; postgres"# shipment_id INTEGER; postgres"# right_now timestamp; postgres"# BEGIN postgres"# right_now := ""now""; postgres"# INSERT INTO shipments VALUES ( 3, customer_id, isbn, right_now ); postgres"# RETURN right_now; postgres"# END; postgres"# " LANGUAGE "plpgsql"; CREATE FUNCTION postgres=# postgres=# select add_shipment(1,"newItem");

       add_shipment

---

2006-10-09 10:06:16.195-07

(1 row) postgres=# postgres=# select * from shipments;

id | customer_id |    isbn    |         ship_date

---

+-------------+------------+----------------------------

 2 |         107 | 0394800753 | 2001-09-22 20:18:56-07
 3 |           1 | newItem    | 2006-10-09 10:06:16.195-07

(2 rows) postgres=# postgres=# drop table shipments; DROP TABLE postgres=#

      </source>