copy from with trigger

Поиск
Список
Период
Сортировка
От Chris spotts
Тема copy from with trigger
Дата
Msg-id 1238985366.17829.5.camel@chris-laptop
обсуждение исходный текст
Ответы Re: copy from with trigger  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
I'm trying to copy from a tab delimited file.  The dates inside the file
are Unix timestamp style dates.
I thought the following script would do the trick, but it just gives me
an error saying
ERROR:  invalid input syntax for type timestamp: "1238736600"
CONTEXT:  COPY testtable line 1, column acquire_time: "1238736600"

Its mapping the right value to the write column, but it doesn't appear
to be going through the trigger.
Here's the relevant setup info.  Just assume the upload file is one unix
style date.

Thanks for any help, I'm rather confused.

CREATE TABLE testtable
(
  acquire_time timestamp without time zone NOT NULL
);
CREATE FUNCTION importData() RETURNS trigger AS $$
    BEGIN
    NEW.acquire_time := TIMESTAMP 'epoch' + int4(NEW.acquire_time) *
INTERVAL '1 SECOND';
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER btestinsert
  BEFORE INSERT
  ON testtable
  FOR EACH ROW
  EXECUTE PROCEDURE importdata();


COPY testtable
(
  acquire_time
)
FROM '/home/testy/test.tab' WITH DELIMITER E'\t' CSV;



В списке pgsql-general по дате отправления:

Предыдущее
От: Colin Streicher
Дата:
Сообщение: Re: Connect to server PG from laptop java app
Следующее
От: Kashmir
Дата:
Сообщение: user defined aggregate for percentile calculations