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) DEFAULT NULL
   7   , mél VARCHAR(100) DEFAULT NULL
   8   , facturable BOOLEAN DEFAULT TRUE NOT NULL
   9   , CONSTRAINT "PK_Client" PRIMARY KEY(ID)
  10   , CONSTRAINT "FK_Client_lieu"
  11       FOREIGN KEY(lieu)
  12       REFERENCES "Lieu"(ID)
  13       ON UPDATE CASCADE
  14   );
  15
  16 DROP FUNCTION choisirUnClient IF EXISTS CASCADE;
  17 CREATE FUNCTION choisirUnClient()
  18   RETURNS TABLE(nom_complet VARCHAR(100), ID INTEGER)
  19   READS SQL DATA
  20   BEGIN ATOMIC
  21     RETURN TABLE
  22       (SELECT nom || ' (' || "Lieu".nom || ')' AS nom_complet
  23              , ID
  24       FROM "Client"
  25       JOIN "Lieu" ON "Lieu".ID = "Client".lieu
  26       );
  27   END;
  28 .;