Mega Code Archive

Using timestamp values correctly

postgres=# postgres=# CREATE TABLE "shipments" ( postgres(# "id" integer DEFAULT nextval('"shipments_ship_id_seq"'::text) NOT NULL, postgres(# "customer_id" integer, postgres(# "isbn" text, postgres(# "ship_date" timestamp with time zone postgres(# ); CREATE TABLE postgres=# postgres=# insert into shipments values (375, 142, '039480001X','2004-01-06 09:19:21-01'); INSERT 0 1 postgres=# insert into shipments values (323, 671, '0451160916','2004-02-14 10:26:41-02'); INSERT 0 1 postgres=# insert into shipments values (998, 1045, '0590445065','2004-03-12 12:39:47-03'); INSERT 0 1 postgres=# postgres=# postgres=# -- Using timestamp values correctly postgres=# postgres=# CREATE FUNCTION add_shipment (integer, text) RETURNS timestamp AS ' postgres'# DECLARE postgres'# -- Declare aliases for function arguments. postgres'# customer_id ALIAS FOR $1; postgres'# isbn ALIAS FOR $2; postgres'# postgres'# shipment_id INTEGER; postgres'# right_now timestamp; postgres'# postgres'# BEGIN postgres'# right_now := ''now''; postgres'# postgres'# SELECT INTO shipment_id id FROM shipments ORDER BY id DESC; postgres'# postgres'# shipment_id := shipment_id + 1; postgres'# postgres'# INSERT INTO shipments VALUES ( shipment_id, customer_id, isbn, right_now ); postgres'# postgres'# RETURN right_now; postgres'# END; postgres'# ' LANGUAGE 'plpgsql'; CREATE FUNCTION postgres=# postgres=# select add_shipment(1, '123'); add_shipment ------------------------- 2006-10-18 19:15:49.125 (1 row) postgres=# postgres=# select * from shipments; id | customer_id | isbn | ship_date -----+-------------+------------+---------------------------- 375 | 142 | 039480001X | 2004-01-06 02:19:21-08 323 | 671 | 0451160916 | 2004-02-14 04:26:41-08 998 | 1045 | 0590445065 | 2004-03-12 07:39:47-08 999 | 1 | 123 | 2006-10-18 19:15:49.125-07 (4 rows) postgres=# postgres=# drop FUNCTION add_shipment (integer, text); DROP FUNCTION postgres=# drop table shipments; DROP TABLE postgres=# postgres=#