How to execute TWO OR MORE Sql statement?

Dan O'Neill freeradius at northpb.com
Wed Jul 26 16:43:31 CEST 2006


Alan Lumb wrote:
>>Create a stored procedure in the database that contains all of the SQL
>>queries necessary. Then call that stored procedure via sql.conf. This
>>works fine with Postgres.
> 
> 
> MySQL 5 supports stored proceedures and functions, however I know that
> mysql proceedures can cause problems as they can/will return multiple data
> sets that can cause some apps problems (not sure about freeradius and its
> support for mysql).

Stored procedures have to be designed to return one or more rows.

Here's an example. I have heartbeat monitor running against Freeradius
to monitor authorization on a regular basis. I do not want these
heartbeat authentications written into the 'radpostauth' table.  The
username 'TylerDurden' is filtered out prior to inserting the postauth
record.

Here is the Postgres stored procedure in the RADIUS database:

CREATE FUNCTION filtered_insert_radpostauth(text, text, text) RETURNS
integer
     AS $_$
DECLARE _new_filtered_insert_radpostauth_id  integer;
BEGIN
     IF ( $1 = 'TylerDurden')
     THEN
         RETURN 0;
     END IF;

     INSERT INTO radpostauth (username, pass, reply, authdate)
                 VALUES ( $1, $2, $3, NOW() );
     IF FOUND
     THEN
         SELECT INTO  _new_filtered_insert_radpostauth_id  MAX(id)
           FROM radpostauth;
         RETURN       _new_filtered_insert_radpostauth_id;
     END IF;

     return -1;
END
$_$
     LANGUAGE plpgsql;


ALTER FUNCTION public.filtered_insert_radpostauth(text, text, text)
OWNER TO postgres;

and the line in postgresql.conf looks like (it's wrapped here):

postauth_query = "SELECT filtered_insert_radpostauth( '%{User-Name}',
'%{User-Password:-Chap-Password}', '%{reply:Packet-Type}' )"

Everyone but TylerDurden gets logged because he doesn't really exist anyway.

Good luck!

Dan






More information about the Freeradius-Users mailing list