Dec 302011
 

After a very long effort, this is the solution:

CREATE OR REPLACE FUNCTION conflict.conflict_statistics(conference_id integer, OUT conflict_level text, OUT conflicts integer)
 RETURNS SETOF record
 LANGUAGE plpgsql
AS $function$
#variable_conflict use_variable
  DECLARE
    conflict TEXT;
    conflict_rows INTEGER;
  BEGIN
    FOR conflict_level IN
      SELECT conflict_level.conflict_level FROM conflict.conflict_level WHERE conflict_level.conflict_level <> 'silent' ORDER BY rank
    LOOP
      conflicts = 0;
        FOR conflict IN
          SELECT conference_phase_conflict.conflict
          FROM
            conflict.conference_phase_conflict
            INNER JOIN conference ON (
              conference.conference_id = conference_id AND
              conference.conference_phase = conference_phase_conflict.conference_phase )
          WHERE conference_phase_conflict.conflict_level = conflict_level
        LOOP
          EXECUTE 'SELECT count(1) FROM conflict.conflict_' || conflict || '(' || conference_id || ');' INTO STRICT conflict_rows;
          conflicts := conflicts + conflict_rows;
        END LOOP;
      RETURN NEXT;
    END LOOP;
  END;
$function$

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive

  One Response to “Pentabarf fix”

  1. See also:

    CREATE OR REPLACE FUNCTION auth.activate_account(activation_string character)
    RETURNS integer
    LANGUAGE plpgsql
    STRICT
    AS $function$
    #variable_conflict use_variable
    DECLARE
    cur_activation RECORD;
    cur_person_id INTEGER;
    activation_interval INTERVAL;
    BEGIN
    activation_interval = ‘-7 day’;
    DELETE FROM auth.account WHERE account_id IN ( SELECT account_id FROM auth.account_activation WHERE account_creation < now() + activation_interval );
    SELECT INTO cur_activation account_id, conference_id FROM auth.account_activation WHERE account_activation.activation_string = activation_string;
    IF FOUND THEN
    INSERT INTO auth.account_role(account_id, role) VALUES (cur_activation.account_id, ‘submitter’);
    SELECT INTO cur_person_id nextval(pg_get_serial_sequence(‘base.person’,’person_id’));
    INSERT INTO person(person_id,nickname,email) SELECT cur_person_id,login_name,email FROM auth.account WHERE account.account_id = cur_activation.account_id;
    UPDATE auth.account SET person_id = cur_person_id WHERE account_id = cur_activation.account_id;
    INSERT INTO auth.account_settings(account_id,current_conference_id) VALUES (cur_activation.account_id, cur_activation.conference_id);
    DELETE FROM auth.account_activation WHERE account_activation.activation_string = activation_string;
    ELSE
    PERFORM 1 FROM log.account_activation WHERE account_activation.activation_string = activation_string AND account_activation.log_operation = ‘D’;
    IF FOUND THEN
    RAISE EXCEPTION ‘Your account has already been activated.’;
    ELSE
    RAISE EXCEPTION ‘Invalid activation string.’;
    END IF;
    END IF;
    RETURN cur_activation.conference_id;
    END;
    $function$