Eksempler for å komme i gang med SQL3 i Postgres

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
    

Brukerdefinerte datatyper

Triggere

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

Eksempel 1: If-test på tall-verdi

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)

Eksempel 2: Debug-test

Hvis man ønsker å skrive ut informasjon til bruker i en trigger(for eksempel for debugging), bruker man kommandoen RAISE NOTICE.

create table debugTest(
  id int not null,
  verdi varchar(10),
  primary key(id));

create function write_debug() returns trigger as '
declare
begin
  RAISE NOTICE ''Dette er en debug-melding'';
  RAISE NOTICE ''id=%, verdi=%'', NEW.id, NEW.verdi;

  return NEW;
end
' language 'plpgsql';

CREATE TRIGGER debugTrigger BEFORE INSERT OR UPDATE
     ON debugTest FOR EACH ROW
     EXECUTE PROCEDURE write_debug();

insert into debugTest values(1, 'blabla');
NOTICE:  Dette er en debug-melding
NOTICE:  id=1, verdi=blabla
INSERT 4984147 1
insert into debugTest values(2, 'haha');
NOTICE:  Dette er en debug-melding
NOTICE:  id=2, verdi=haha
INSERT 4984148 1
insert into debugTest values(3, 'kaklekakle');
NOTICE:  Dette er en debug-melding
NOTICE:  id=3, verdi=kaklekakle
INSERT 4984149 1

SELECT * FROM debugTest;
 id | verdi
----+------------
  1 | blabla
  2 | haha
  3 | kaklekakle
(3 rows)

Eksempel 3: En manuell foreign key trigger med array

create table fktest(
  id int not null,
  navn varchar(10),
  primary key(id));

create table fktest_array(
  id int not null,
  fktest_id int[10],
  primary key(id));

create function manualFK() returns trigger as '
declare
  tmp_val integer := 0;
  ids_in_fktest record;
  found_val boolean := false;
begin
  for i IN 1..10 loop
    for ids_in_fktest in select id from fktest loop
      select into tmp_val NEW.fktest_id[i];
      if tmp_val = ids_in_fktest.id then
        found_val := true;
      elsif tmp_val is null then
        found_val := true;
        exit;
      end if;
    end loop;

    if not found_val then
        raise exception ''Illegal values found in array'';
    end if;

    found_val := false;
  end loop;

  return NEW;
end
' language 'plpgsql';

CREATE TRIGGER manualfkt BEFORE INSERT OR UPDATE
     ON fktest_array FOR EACH ROW
     EXECUTE PROCEDURE manualFK();

insert into fktest values(1, 'blabla');
insert into fktest values(2, 'clacla');
insert into fktest values(3, 'dladla');
insert into fktest values(4, 'elaela');

insert into fktest_array values(1, '{1, 2, 3, 4}');
INSERT 4977937 1
insert into fktest_array values(2, '{1, 2, 3, 5}');
ERROR:  Illegal values found in array

SELECT * FROM fktest_array;
 id | fktest_id 
----+-----------
  1 | {1,2,3,4}
(1 row)

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

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)