PostgreSQL/Postgre SQL/Insert Data — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 13:13, 26 мая 2010
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>