Datentyp umwandeln bzw. Daten implizit anpassen

Hallo an alle,

bin neu in postgresql bin aber ansonsten in gängigen Programiersprachen und mySQL relativ fit.
Da ich mich jetzt seit kurzer Zeit mit postgresql beschägtigen muß … inzwischen auch mit immer mehr Spaß … wegen doch toller Möglichkeit hat sich mir jetzt ein Problem aufgetan das ich gerne elegant lösen will.

 
CREATE TABLE user (
 id SERIAL,
 email VARCHAR(128) UNIQUE NOT NULL,
 username VARCHAR(32) UNIQUE NOT NULL CHECK (length(trim(username))>3),
 create TIMESTAMP,
) WITHOUT OIDS;
ALTER TABLE user ADD PRIMARY KEY (id);
CREATE TABLE attributes (
 id INTEGER NOT NULL,
 key VARCHAR(16) NOT NULL,
 text TEXT,
 binary BYTEA,
 modified_at TIMESTAMP,
 modified_by INTEGER
) WITHOUT OIDS;
CREATE INDEX idx_id ON attributes (id);
CREATE INDEX idx_key ON attributes (key);
ALTER TABLE  attributes ADD FOREIGN KEY (id) REFERENCES user (id) ON UPDATE RESTRICT ON DELETE RESTRICT;
CREATE FUNCTION stamp_it() RETURNS trigger AS $stamp_it$
    BEGIN
        IF OLD.modified_by IS NULL THEN
         NEW.modified_by := 1;
        ELSE
         IF [color=red][B]NEW.modified_by typeof INTEGER[/B][/color] ([B]???[/B]) THEN
          NEW.modified_by := OLD.modified_by;
         ELSE
          SELECT INTO NEW.modified_by id FROM user WHERE username=OLD.modified_by;
         END IF;
       END IF;
        NEW.modified_at := current_timestamp;
        RETURN NEW;
    END;
$stamp_it$ LANGUAGE plpgsql;
CREATE TRIGGER stamp_it BEFORE INSERT OR UPDATE ON attributes FOR EACH ROW EXECUTE PROCEDURE stamp_it();
  1. Ich bin mir nicht im klaren ob folgens prinzipiell geht: Ich übergebe einer INSERT oder UPDATE Anweisung einen String und ein Trigger wandelt diesen String (User) in einen Integerwert um (via Select) und ersetzt diesen im Datensatz.

  2. Gibt es eine Funktion oder Operator wie gettype() in PHP oder typeof in Java mit der ich in der Funktion bestimmen kann was es für einen type ich da habe.

  3. diesen Varianten sollen funktionieren und durch “innere” Logik ordentlich behandelt werden

INSERT INTO attributes (id,key,text) VALUES (1,'nickname','Smiley')
 INSERT INTO attributes (id,key,text,modified_by) VALUES (1,'nickname','Smiley','admin')
 INSERT INTO attributes (id,key,text,modified_by) VALUES (1,'nickname','Smiley',1)
  1. Gibt es eine elegantere Lösung?

  2. Würdet ihr lieber character varying(n) benutzen statt des Alias varchar(n) ? dito für alle anderen aliase…

Vielen Dank für die Hilfe.

Über TG_TABLE_NAME hast Du den namen der Tabelle, über

select data_type from information_schema.columns where table_name='...' and column_name = '...';

kannst Du nun den Datentyp erfragen.

Andreas

Nein es ging mir nicht um den Typ der in der Datenbank definiert ist, sondern um den Typ der von INSERT bzw. UPDATE Anweisung übergeben wird … oder ist das etwa immer eine Zeichenkette egal ob ich

  • INSERT INTO … VALUES(1000)

oder

  • INSERT INTO … VALUES(‘tausend’)

oder

  • INSERT INTO … VALUES(1000.00)

oder

  • INSERT INTO … VALUES(1E3)

schreibe?

Danke für alle Infos.

Ahh.

Nein, wird so nicht gehen, du wirst einen Syntaxfehler bekommen, wenn die Spalte INT ist und Du einen nicht-castfähigen String übergibst. Wohlgemerkt, Du kannst 1 oder auch ‘1’ übergeben, das geht, Aber nicht ‘eins’ und dann erst im Trigger daraus 1 machen. Da kommt vorher die Syntaxprüfung.

test=# create table foo (i int);
CREATE TABLE
test=*# create or replace function trg_foo() returns trigger as $$begin if length(regexp_replace(NEW.i::text,'[0-9]','','g')) > 1 then NEW.i = 1; end if; return NEW; end; $$languageplpgsql;
CREATE FUNCTION
test=*# create trigger trg_foo before insert or update on foo for each row execute procedure trg_foo();
CREATE TRIGGER
test=*# insert into foo values (1);
INSERT 0 1
test=*# insert into foo values ('1');
INSERT 0 1
test=*# insert into foo values ('eins');
ERROR:  invalid input syntax for integer: "eins"

Du könnest Dir eine Funktion schreiben, ähnlich meiner Triggerfunktion.


Andreas

Hallo,

dann ist der weg über eine trigger falsch, und ich werde es mit einer eigenen funktion make_user_id() versuchen, die arbeitet dann vor dem cast.

 
INSERT INTO attributes (id,key,text) VALUES (1,'nickname','Smiley')
INSERT INTO attributes (id,key,text,modified_by) VALUES (1,'nickname','Smiley',make_user_id('admin'))
INSERT INTO attributes (id,key,text,modified_by) VALUES (1,'nickname','Smiley',make_user_id(1))

Ergebnis folgt …