sql/init/Client.sql

   1 \p CREATE TABLE Client
   2 CREATE CACHED TABLE "Client"
   3   ( ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL
   4   , nom VARCHAR(100) NOT NULL
   5   , lieu INTEGER NOT NULL
   6   , tél VARCHAR(100)
   7   , mél VARCHAR(100)
   8   , CONSTRAINT "PK_Client" PRIMARY KEY(ID)
   9   );
  10 ALTER TABLE "Client"
  11   ADD CONSTRAINT "FK_Client_lieu" FOREIGN KEY(lieu) REFERENCES "Lieu"(ID)
  12   ON UPDATE CASCADE;
  13
  14 DROP FUNCTION choisirUnClient IF EXISTS CASCADE;
  15 CREATE FUNCTION choisirUnClient()
  16   RETURNS TABLE(nom_complet VARCHAR(100), ID INTEGER)
  17   READS SQL DATA
  18   BEGIN ATOMIC
  19     RETURN TABLE
  20       (SELECT nom || ' (' || "Lieu".nom || ')' AS nom_complet
  21              , ID
  22       FROM "Client"
  23       JOIN "Lieu" ON "Lieu".ID = "Client".lieu
  24       );
  25   END;
  26 .;