plpgsql-function with timestamp

Поиск
Список
Период
Сортировка
От Christian Stalp
Тема plpgsql-function with timestamp
Дата
Msg-id 22783.1140546890@www065.gmx.net
обсуждение исходный текст
Ответы Re: plpgsql-function with timestamp  (Andreas Kretschmer <akretschmer@spamfence.net>)
Список pgsql-novice
Hello out there,
Im a postgreSQL-newbie and I jaust want to create a "plpgsql-function" for
writing into a table.

The table is here:
CREATE TABLE auktionen(
    aid SERIAL PRIMARY KEY,
    kid INTEGER NOT NULL,
    name VARCHAR(25) NOT NULL,
    frei CHAR(1) DEFAULT 'Y',
    status CHAR(1) DEFAULT '1', -- 1: In Vorbereitung
    sofort NUMERIC(16,2) DEFAULT 0,
    foto VARCHAR(25),
    beschreibung VARCHAR(150),
    startzeit TIMESTAMP NOT NULL,
    endzeit TIMESTAMP NOT NULL,
    startpreis NUMERIC(16,2) DEFAULT 0,
    preis NUMERIC(16,2) DEFAULT 0,
    katid INTEGER NOT NULL,
    FOREIGN KEY(katid)
    REFERENCES kategorie(katid),
    FOREIGN KEY(kid)
    REFERENCES kunden(kid)
);


And the function is here:

CREATE OR REPLACE FUNCTION neue_auktion ( numeric, text, text, TIMESTAMP,
TIMESTAMP, numeric, numeric, numeric ) RETURNS text AS $$
DECLARE
    my_kunden_id    ALIAS FOR $1;
    my_name         ALIAS FOR $2;
    my_beschreibung ALIAS FOR $3;
    my_startzeit    ALIAS FOR $4;
    my_endzeit      ALIAS FOR $5;
    my_startpreis   ALIAS FOR $6;
    my_preis        ALIAS FOR $7;
    my_kategorie    ALIAS FOR $8;

BEGIN

   INSERT INTO auktionen ( kid, name, beschreibung, startzeit, endzeit,
startpreis, preis, katid ) VALUES ( my_kunden_id, my_name, my_beschreibung,
my_startzeit, my_endzeit, my_startpreis, my_startpreis, my_kategorie );

   RETURN 'OK';
END;
$$
LANGUAGE plpgsql;

But when I call this function, I get this dump:
test2=# SELECT neue_auktion ( 1, 'robot', 'robot', '1999-01-08 04:05:06',
'1999-01-08 04:05:06', 10, '10', '1');
FEHLER:  Spalte »startzeit« hat Typ timestamp without time zone, aber der
Ausdruck hat Typ text
HINT:  Sie müssen den Ausdruck umschreiben oder eine Typumwandlung
vornehmen.
CONTEXT:  SQL-Anweisung »INSERT INTO auktionen ( kid, name, beschreibung,
startzeit, endzeit, startpreis, preis, katid ) VALUES (  $1 ,  $2 ,  $3 ,
$4 ,  $5 ,  $6 ,  $7 ,  $8  )«
PL/pgSQL function "neue_auktion" line 13 at SQL statement

Its in german and means: column >>startzeit<< has typ timestamp without time
zone, but the expression has text. But I have to put it in with quotes,
otherwise I get some syntax-errors!

What can I do here?

Gruss Christian

--
10 GB Mailbox, 100 FreeSMS/Monat http://www.gmx.net/de/go/topmail
+++ GMX - die erste Adresse für Mail, Message, More +++

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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: problems to install PostgreSQL
Следующее
От: larry postgres
Дата:
Сообщение: find a record in range of a number