Eksempler for å komme i gang med Postgres

Til informasjon:
Alle sql-kommandoer skrives som vanlig tekst
Alle kommandolinjeoperasjoner skrives i rød tekst
Alle resultater skrives i grønn tekst

Arv

CREATE TABLE form
(
   fid integer NOT NULL,
   areal float,
   omkrets float,
   PRIMARY KEY(fid)
);

CREATE TABLE sirkel
(
   radius float
)INHERITS (form);

CREATE TABLE rektangel
(
   lengde float,
   bredde float
)INHERITS (form);

INSERT INTO sirkel VALUES(1, 28.26, 18.84, 3);
INSERT INTO sirkel VALUES(2, 78.54, 31.42, 5);

INSERT INTO rektangel VALUES(1, 6, 10, 2, 3);
INSERT INTO rektangel VALUES(2, 42, 26, 7, 6);

SELECT * FROM sirkel;
 fid | areal | omkrets | radius 
-----+-------+---------+--------
   1 | 28.26 |   18.84 |      3
   2 | 78.54 |   31.42 |      5
(2 rows)

SELECT * FROM rektangel;
 fid | areal | omkrets | lengde | bredde 
-----+-------+---------+--------+--------
   1 |     6 |      10 |      2 |      3
   2 |    42 |      26 |      7 |      6
(2 rows)
    

OID som datatype

CREATE TABLE oidtest
(
  id integer NOT NULL,
  oidval oid,
  PRIMARY KEY(id)
);

INSERT INTO oidtest VALUES(1, 1001);
INSERT INTO oidtest VALUES(2, 1002);
INSERT INTO oidtest VALUES(3, 1003);

SELECT * FROM oidtest;

 id | oidval 
----+--------
  1 |   1001
  2 |   1002
  3 |   1003
(3 rows)
    

DATE

CREATE TABLE datetest
(
  id integer NOT NULL,
  navn char(30),
  fodt timestamp,
  PRIMARY KEY(id)
);

INSERT INTO datetest VALUES(1, 'Per Olsen', '1982-28-02 02:12:00');
INSERT INTO datetest VALUES(2, 'Anne Jensen', '1979-10-06 12:45:00');

SELECT * FROM datetest;
 id |              navn              |        fodt         
----+--------------------------------+---------------------
  1 | Per Olsen                      | 1982-02-28 02:12:00
  2 | Anne Jensen                    | 1979-10-06 12:45:00
(2 rows)
    

Arrays

CREATE TABLE trekant
(
   side float[3],
   vinkel float[3]
)INHERITS (form);

INSERT INTO trekant VALUES(1, 3.46, 9.46, '{3.46,4,2}', '{30,60,90}');
INSERT INTO trekant VALUES(2, 3.897, 9, '{3,3,3}', '{60,60,60}');

SELECT * FROM trekant;
 fid | areal | omkrets |    side    |   vinkel   
-----+-------+---------+------------+------------
   1 |  3.46 |    9.46 | {3.46,4,2} | {30,60,90}
   2 | 3.897 |       9 | {3,3,3}    | {60,60,60}
(2 rows)
    

BLOBS

echo "Per er en snill gutt" > databaser/perolsen.txt
chmod 777 databaser

CREATE TABLE blobtest
(
   id integer NOT NULL,
   navn char(50),
   kommentar oid,
   PRIMARY KEY(id)
);

INSERT INTO blobtest VALUES(1, 'Per Olsen', lo_import('/home/ansatte/elinkaan/databaser/perolsen.txt'));

SELECT lo_export(blobtest.kommentar, '/home/ansatte/elinkaan/databaser/kopi_perolsen.txt')
FROM blobtest
WHERE navn = 'Per Olsen';
 lo_export 
-----------
         1
(1 row)

cat databaser/kopi_perolsen.txt
Per er en snill gutt
    

Triggere

Hvis ikke plpgsql eksisterer:
createlang -U elinkaan -d template1 -L /usr/lib/pgsql plpgsql

CREATE TABLE triggerTest
(
  id integer NOT NULL,
  posnr integer,
  negnr integer,
  PRIMARY KEY(id)
);

CREATE FUNCTION testPosNeg() RETURNS TRIGGER AS '
  BEGIN
    IF NEW.posnr < 0 THEN
      RAISE EXCEPTION ''posnr cannot be negative'';
    END IF;

    IF NEW.negnr > 0 THEN
      RAISE EXCEPTION ''negnr cannot be positive'';
    END IF;

    RETURN NEW;
  END
' LANGUAGE 'plpgsql';

CREATE TRIGGER posneg  BEFORE INSERT OR UPDATE
     ON triggertest FOR EACH ROW
     EXECUTE PROCEDURE testPosNeg();

INSERT INTO triggerTest VALUES(1, 1, -1);
INSERT 3237108 1
INSERT INTO triggerTest VALUES(2, 0, -1);
INSERT 3237109 1
INSERT INTO triggerTest VALUES(3, -1, 1);
ERROR:  posnr cannot be negative
INSERT INTO triggerTest VALUES(4, 3, -2);
INSERT 3237110 1
INSERT INTO triggerTest VALUES(5, 1, 1);
ERROR:  negnr cannot be positive
INSERT INTO triggerTest VALUES(6, 1, 0);
INSERT 3237111 1

SELECT * FROM triggerTest;
 id | posnr | negnr 
----+-------+-------
  1 |     1 |    -1
  2 |     0 |    -1
  4 |     3 |    -2
  6 |     1 |     0
(4 rows)
    

For spesielt interesserte: her er et eksempel på hvordan man lager en trigger i C

Rules

CREATE TABLE person
(
  id integer NOT NULL,
  navn char(50),
  sivilstatus char(10),
  PRIMARY KEY(id)
);

CREATE TABLE person_logg
(
  id integer NOT NULL,
  sivilstatus char(10),
  log_naar timestamp,
  log_avhvem text,
  PRIMARY KEY(id, log_naar)
);

CREATE RULE log_sivilstatus AS ON UPDATE TO person
     WHERE NEW.sivilstatus != OLD.sivilstatus
     DO INSERT INTO person_logg VALUES (
                                     NEW.id,
                                     NEW.sivilstatus,
                                     current_timestamp,
                                     current_user
                                 );

INSERT INTO person VALUES(1, 'Per Olsen', 'ugift');

UPDATE person
SET sivilstatus='gift'
WHERE id=1;

UPDATE person
SET sivilstatus='separert'
WHERE id=1;

UPDATE person
SET sivilstatus='gift'
WHERE id=1;

UPDATE person
SET sivilstatus='enkemann'
WHERE id=1;

SELECT * FROM person;
 id |                        navn                        | sivilstatus 
----+----------------------------------------------------+-------------
  1 | Per Olsen                                          | enkemann  
(1 row)

SELECT * FROM person_logg;
 id | sivilstatus |          log_naar          | log_avhvem 
----+-------------+----------------------------+------------
  1 | gift        | 2003-10-09 11:32:50.009128 | elinkaan
  1 | separert    | 2003-10-09 11:33:07.770337 | elinkaan
  1 | gift        | 2003-10-09 11:33:15.870525 | elinkaan
  1 | enkemann    | 2003-10-09 11:33:26.565217 | elinkaan
(4 rows)