Обсуждение: plpgsql-function with timestamp

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 +++

Christian Stalp <christian.stalp@gmx.de> schrieb:
[ problem ]

Which version?

test=# create table foo (ts timestamp);
CREATE TABLE
test=# insert into foo values ('1999-01-08 04:05:06');
INSERT 0 1
test=# CREATE OR REPLACE FUNCTION neue_auktion (TIMESTAMP) returns text
AS $$ DECLARE my_startzeit    ALIAS FOR $1; BEGIN INSERT INTO foo (ts)
VALUES ( my_startzeit); RETURN 'OK'; END; $$ LANGUAGE plpgsql;
CREATE FUNCTION
test=# SELECT neue_auktion ('1999-01-08 04:05:06');
 neue_auktion
--------------
 OK
(1 row)

test=# select version();
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.1.2 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3 20060104 (prerelease) (Debian 4.0.2-6)
(1 row)


Btw.: we have a german mailinglist ;-)


HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

The problem is solfed:
I called the function with this arguments:

SELECT neue_auktion ( 1::numeric, 'robot'::text, 'robot'::text, '1999-01-08
04:05:06'::timestamp, '1999-01-08 04:05:06'::timestamp, 10::numeric,
10::numeric, 1::numeric);

And this works.

Thank you...

Gruss Christian

--
DSL-Aktion wegen gro�er Nachfrage bis 28.2.2006 verl�ngert:
GMX DSL-Flatrate 1 Jahr kostenlos* http://www.gmx.net/de/go/dsl

Hello!

    Does the :: do a data type conversion?

On Wednesday 22 February 2006 13:22, Christian Stalp wrote:
> The problem is solfed:
> I called the function with this arguments:
>
> SELECT neue_auktion ( 1::numeric, 'robot'::text, 'robot'::text, '1999-01-08
> 04:05:06'::timestamp, '1999-01-08 04:05:06'::timestamp, 10::numeric,
> 10::numeric, 1::numeric);
>
> And this works.
>
> Thank you...
>
> Gruss Christian

--
Regards,

Richard Kut
Database Administrator
Research & Development
Intelerad Medical Systems Inc.
460 Ste-Catherine West, Suite 210
Montreal, Quebec, Canada H3B 1A7
Tel:     514.931.6222 x7733
Fax:     514.931.4653
rkut@intelerad.com
www.intelerad.com

This email or any attachments may contain confidential or legally
privileged information intended for the sole use of the addressees. Any
use, redistribution, disclosure, or reproduction of this information,
except as intended, is prohibited. If you received this
email in error, please notify the sender and remove all copies of the
message, including any attachments.

> Hello!
>
>     Does the :: do a data type conversion?
>
Yes, that right. It seemes to be the only way to tell the function, that
this is the right type?!?

Gruss Christian

--
Telefonieren Sie schon oder sparen Sie noch?
NEU: GMX Phone_Flat http://www.gmx.net/de/go/telefonie