User Tools

Site Tools


podoc:count_equipment_by_user_sql_function

Database Function Description of "count_equipment_by_user"

Name

count_equipment_by_user

Description

Count the total amount of equipment items of a particular user.

There are two versions, one that counts all equipment and one that counts only those equipment items up to a given access rights setting.

Purpose

To retrieve the total amount of equipment of a user.

Schema

CREATE FUNCTION count_equipment_by_user(INTEGER) RETURNS BIGINT AS '
        select ((select count(identifier) from lens where users = $1) +
            (select count(identifier) from camera where users = $1) +
            (select count(identifier) from scanner where users = $1) +
            (select count(identifier) from film where users = $1) +
            (select count(identifier) from support where users = $1) +
            (select count(identifier) from flash where users = $1))
' LANGUAGE 'sql';
CREATE FUNCTION count_equipment_by_user(INTEGER, INTEGER) RETURNS BIGINT AS '
        select ((select count(identifier) from lens where users = $1 and access_rights <= $2) +
            (select count(identifier) from camera where users = $1 and access_rights <= $2) +
            (select count(identifier) from scanner where users = $1 and access_rights <= $2) +
            (select count(identifier) from film where users = $1 and access_rights <= $2) +
            (select count(identifier) from support where users = $1 and access_rights <= $2) +
            (select count(identifier) from flash where users = $1 and access_rights <= $2))
' LANGUAGE 'sql';

History

Used first

Version 2.5

Used last

Still in use

Change history

= Version 2.5 =

CREATE FUNCTION count_equipment_by_user(INTEGER) RETURNS BIGINT
AS 'select ((select count(identifier) from lens where users = $1) +
            (select count(identifier) from camera where users = $1) +
            (select count(identifier) from flash where users = $1) +
            (select count(identifier) from scanner where users = $1) +
            (select count(identifier) from film where users = $1) +
            (select count(identifier) from support where users = $1) +
            (select count(identifier) from flash where users = $1))' LANGUAGE 'sql';
CREATE FUNCTION count_equipment_by_user(INTEGER, INTEGER) RETURNS BIGINT
AS 'select ((select count(identifier) from lens where users = $1 and access_rights <= $2) +
            (select count(identifier) from camera where users = $1 and access_rights <= $2) +
            (select count(identifier) from flash where users = $1 and access_rights <= $2) +
            (select count(identifier) from scanner where users = $1 and access_rights <= $2) +
            (select count(identifier) from film where users = $1 and access_rights <= $2) +
            (select count(identifier) from support where users = $1 and access_rights <= $2) +
            (select count(identifier) from flash where users = $1 and access_rights <= $2))' LANGUAGE 'sql';

= Version 2.34 =

---- Fix up a couple of bogus functions
CREATE OR REPLACE FUNCTION count_equipment_by_user(INTEGER) RETURNS BIGINT AS '
        select ((select count(identifier) from lens where users = $1) +
            (select count(identifier) from camera where users = $1) +
            (select count(identifier) from scanner where users = $1) +
            (select count(identifier) from film where users = $1) +
            (select count(identifier) from support where users = $1) +
            (select count(identifier) from flash where users = $1))
' LANGUAGE 'sql';
 
CREATE OR REPLACE FUNCTION count_equipment_by_user(INTEGER, INTEGER) RETURNS BIGINT AS '
        select ((select count(identifier) from lens where users = $1 and access_rights <= $2) +
            (select count(identifier) from camera where users = $1 and access_rights <= $2) +
            (select count(identifier) from scanner where users = $1 and access_rights <= $2) +
            (select count(identifier) from film where users = $1 and access_rights <= $2) +
            (select count(identifier) from support where users = $1 and access_rights <= $2) +
            (select count(identifier) from flash where users = $1 and access_rights <= $2))
' LANGUAGE 'sql';
podoc/count_equipment_by_user_sql_function.txt · Last modified: 2007/10/19 16:08 by Luud