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)
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;
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;
