Moved unused and unneeded stored procedure to a separate file

[freeradius.git] / doc / examples / postgresql.sql

diff --git a/doc/examples/postgresql.sql b/doc/examples/postgresql.sql
index 49c6f7b..bd897cd 100644 (file)
--- a/doc/examples/postgresql.sql
+++ b/doc/examples/postgresql.sql
@@ -18,7 +18,6 @@ CREATE TABLE radacct (
        AcctSessionId           VARCHAR(32) NOT NULL,
        AcctUniqueId            VARCHAR(32) NOT NULL,
        UserName                VARCHAR(253),
-       GroupName               VARCHAR(253),
        Realm                   VARCHAR(64),
        NASIPAddress            INET NOT NULL,
        NASPortId               VARCHAR(15),
@@ -35,11 +34,11 @@ CREATE TABLE radacct (
        CallingStationId        VARCHAR(50),
        AcctTerminateCause      VARCHAR(32),
        ServiceType             VARCHAR(32),
-       XAscendSessionSvrKey    VARCHAR(10),
        FramedProtocol          VARCHAR(32),
        FramedIPAddress         INET,
        AcctStartDelay          BIGINT,
-       AcctStopDelay           BIGINT
+       AcctStopDelay           BIGINT,
+       XAscendSessionSvrKey    VARCHAR(10)
 );
 -- This index may be usefull..
 -- CREATE UNIQUE INDEX radacct_whoson on radacct (AcctStartTime, nasipaddress);
@@ -129,18 +128,18 @@ create index radreply_UserName on radreply (UserName,Attribute);
 -- create index radreply_UserName_lower on radreply (lower(UserName),Attribute);
 
 /*
- * Table structure for table 'radusergroup'
+ * Table structure for table 'usergroup'
  */
-CREATE TABLE radusergroup (
+CREATE TABLE usergroup (
        UserName        VARCHAR(64) NOT NULL DEFAULT '',
        GroupName       VARCHAR(64) NOT NULL DEFAULT '',
        priority        INTEGER NOT NULL DEFAULT 0
 );
-create index radusergroup_UserName on radusergroup (UserName);
+create index usergroup_UserName on usergroup (UserName);
 /*
  * Use this index if you use case insensitive queries
  */
--- create index radusergroup_UserName_lower on radusergroup (lower(UserName));
+-- create index usergroup_UserName_lower on usergroup (lower(UserName));
 
 /*
  * Table structure for table 'realmgroup'
@@ -185,14 +184,12 @@ create index nas_nasname on nas (nasname);
 --
 
 CREATE TABLE radpostauth (
-       id                      BIGSERIAL PRIMARY KEY,
-       username                VARCHAR(253) NOT NULL,
-       pass                    VARCHAR(128),
-       reply                   VARCHAR(32),
-       CalledStationId         VARCHAR(50),
-       CallingStationId        VARCHAR(50),
-       authdate                TIMESTAMP with time zone NOT NULL default 'now'
-);
+       id              BIGSERIAL PRIMARY KEY,
+       username        VARCHAR(253) NOT NULL,
+       pass            VARCHAR(128),
+       reply           VARCHAR(32),
+       authdate        TIMESTAMP with time zone NOT NULL default 'now'
+) ;
 
 --
 -- Table structure for table 'radippool'
@@ -200,21 +197,16 @@ CREATE TABLE radpostauth (
 
 CREATE TABLE radippool (
        id                      BIGSERIAL PRIMARY KEY,
-       pool_name               varchar(64) NOT NULL,
-       FramedIPAddress         INET NOT NULL,
-       NASIPAddress            VARCHAR(16) NOT NULL default '',
-       pool_key                VARCHAR(64) NOT NULL default 0,
+       pool_name               text NOT NULL,
+       FramedIPAddress         INET,
+       NASIPAddress            text NOT NULL,
        CalledStationId         VARCHAR(64),
-       CallingStationId        text NOT NULL default ''::text,
-       expiry_time             TIMESTAMP(0) without time zone NOT NULL default 'now'::timestamp(0),
-       username                text DEFAULT ''::text
+       CallingStationId        text NOT NULL DEFAULT ''::text,
+       expiry_time             TIMESTAMP(0) without time zone NOT NULL,
+       username                text DEFAULT ''::text,
+       pool_key                VARCHAR(30) NOT NULL
 );
 
-CREATE INDEX radippool_poolname_ipaadr ON radippool USING btree (pool_name, framedipaddress);
-CREATE INDEX radippool_poolname_expire ON radippool USING btree (pool_name, expiry_time);
-CREATE INDEX radippool_nasipaddr_poolkey ON radippool USING btree (nasipaddress, pool_key);
-CREATE INDEX radippool_nasipaddr_calling ON radippool USING btree (nasipaddress, callingstationid);
-
 --
 -- Table structure for table 'dictionary'
 -- This is not currently used by FreeRADIUS
@@ -228,28 +220,3 @@ CREATE INDEX radippool_nasipaddr_calling ON radippool USING btree (nasipaddress,
 --     Vendor          VARCHAR(32)
 -- );
 
-/*
- * Note: (pnixon: 2003-12-10) The following function should not be required
- * if you use the PG specific queries in raddb/postgresql.conf
- *
- * Common utility function for date calculations. This is used in our
- * alternative account stop query to calculate the start of a session.
- *
- * This function is Copyright 2001 by Mark Steele (msteele@inet-interactif.com)
- *
- * Please note that this requires the plpgsql to be available in your
- * radius database. If it is not available you can register it with
- * postgres by running this command:
- *
- *   createlang plpgsql <databasename>
- */
-CREATE FUNCTION DATE_SUB(date,int4,text) RETURNS DATE AS '
-DECLARE
-        var1 date;
-        var2 text;
-BEGIN
-        var2 = $2 || '' '' || $3;
-        SELECT INTO var1
-                to_date($1 - var2::interval, ''YYYY-MM-DD'');
-RETURN var1;
-END;' LANGUAGE 'plpgsql';