Re: [GENERAL] timestamps

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;