Mega Code Archive

 

Categories / PostgreSQL / Postgre SQL

 

Using function to insert data to a table

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=#