[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] timestamps
From:       "jose' soares" <sferac () bo ! nettuno ! it>
Date:       1999-02-24 14:39:32
[Download RAW message or body]

Questo è un messaggio multi-parte scritto in formato MIME.

strawman@plexi.com ha scritto:

> I'm trying to create a column that defaults to the current time and date. I
> tried the SQLServer like syntax below but potgresql choked:
>
> CREATE TABLE clicks (
>     avo_userid varchar (10) NOT NULL ,
>     link_id int NOT NULL ,
>     the_time timestamp NOT NULL CONSTRAINT df_now DEFAULT (timestamp('now'))
> );
> NOTICE:  there is more than one function named "timestamp"
> NOTICE:  that satisfies the given argument types. you will have to
> NOTICE:  retype your query using explicit typecasts.
> ERROR:  function timestamp(unknown) does not exist
>
> Is "timestamp" not both a type and a function? How can I look it up?
>

>

You can create table like:

CREATE TABLE clicks (
    userid    varchar(10) not null,
    links    int    not null,
    df_time    timestamp constraint df_now DEFAULT  current_timestamp
);

but remember in such case you can insert a value to df_time column different
than current timestamp.
If you want to avoid this you have to create a trigger (see attached example).

--

>
> And one more question: How does one construct a foreign key relationship in
> postgres?
>
> Thanks for any help.

Foreign key is not yet implemented but you may use triggers (See attached
example).

NB: You need v6.4.? to use examples.


--
- Jose' -

"No other success in life can compensate for failure in the home" (David O.
McKay)


["version.sql" (text/plain)]

DROP TABLE version_test;
CREATE TABLE version_test (
        nome		text,
	username	char(10),	-- user name
	version	timestamp	-- last update
);

drop function f_version();
create function f_version() returns opaque as '
begin
	new.version:= current_timestamp;
	new.username:= current_user;
 	return new;
end;
' language 'plpgsql';

CREATE TRIGGER t_version
	BEFORE INSERT OR UPDATE ON version_test
	FOR EACH ROW 
	EXECUTE PROCEDURE f_version();

INSERT INTO version_test VALUES ('jose','marco','1998-12-31 12:30:00');
INSERT INTO version_test VALUES ('miriam');
update version_test set username='jose';

SELECT * FROM version_test;

["trigger.sql" (text/plain)]

DROP TABLE header;
CREATE TABLE header (
        distretto		CHAR(4)  NOT NULL,
	anno			DECIMAL(4)  NOT NULL,
	numero			INTEGER  NOT NULL,
	data			DATE 	 NOT NULL,
	azienda			CHAR(11) NOT NULL,
        CONSTRAINT k_header PRIMARY KEY (distretto,anno,numero)
	);

DROP TABLE detail;
CREATE TABLE detail (
        distretto		CHAR(4)  NOT NULL,
	anno			DECIMAL(4) NOT NULL,
	numero			INTEGER NOT NULL,
	cod_prestazione		CHAR(05) NOT NULL,
        quantita		FLOAT(4) NOT NULL,
	importo			FLOAT(8),
        CONSTRAINT k_detail PRIMARY KEY (distretto,anno,numero,cod_prestazione),
        CONSTRAINT k_extern FOREIGN KEY(distretto,anno,numero) references HEADER
	);

drop function f_not_add_detail();
create function f_not_add_detail() returns opaque as '
declare
	/* to avoid insert detail if header doesn''t exist */
	tot int;
begin
	select numero into tot from header
        where anno = new.anno and numero = new.numero;
	if not found then
		raise notice ''Impossible add new detail!'';
		return NULL;
	else
    		return new;
	end if;
end;
' language 'plpgsql';

create trigger t_not_add_detail before insert
    on detail for each row execute procedure f_not_add_detail();


--EXAMPLE:
select * from header;
select * from detail;
INSERT INTO detail VALUES ('E14','1999',2,'IMPOSSIBLE',123,345.5); --impossible
INSERT INTO header VALUES ('E14','1999',2,current_date,1235);
INSERT INTO detail VALUES ('E14','1999',2,'AB',523,35.5);
INSERT INTO header VALUES ('E14','1999',1,current_date,1235);
INSERT INTO detail VALUES ('E14','1999',1,'A1',423,45.5);
INSERT INTO detail VALUES ('E14','1999',1,'AC',123,345.5);
select * from header;
select * from detail;

drop function f_upd_key_detail();
create function f_upd_key_detail() returns opaque as '
declare
	/* change in cascade the key of every detail if header key is changed */
	tot int;
begin
	update detail 
          set anno = new.anno, numero = new.numero
	  where anno = old.anno and numero = old.numero;
    	return NULL;
end;
' language 'plpgsql';

drop trigger t_upd_key_detail on header;
create trigger t_upd_key_detail after update
    on header for each row execute procedure f_upd_key_detail();

--EXAMPLE:
select * from header;
select * from detail;
update header set anno='1997', numero=33 where numero = 1 and anno='1999';
select * from header;
select * from detail;


drop function f_del_cascade();
create function f_del_cascade() returns opaque as '
declare
	/* cancel in cascade all details after header is deleted */
begin
	delete from detail
        where anno = old.anno and numero = old.numero;
    	return NULL;
end;
' language 'plpgsql';

drop trigger t_del_cascade on header;
create trigger t_del_cascade after delete
    on header for each row execute procedure f_del_cascade();

--EXAMPLE:
select * from header;
select * from detail;
delete from header where anno = 1997;
select * from header;
select * from detail;


[prev in list] [next in list] [prev in thread] [next in thread]