в 13:45, 26 мая 2010

2010-05-26T13:45:56Z

Admin: 1 версия

2010-05-26T10:13:54Z

1 версия

Новая страница

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

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

PostgreSQL/Postgre SQL/Insert Data - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия