-- Function: itemcharvalue(integer, text)

-- DROP FUNCTION itemcharvalue(integer, text);

CREATE OR REPLACE FUNCTION charass_getvalue(text, integer, text)
  RETURNS text AS
$BODY$
-- Copyright (c) 1999-2011 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pType ALIAS FOR $1;
  pItemid ALIAS FOR $2;
  pCharName ALIAS FOR $3;
  _value TEXT;

BEGIN

  SELECT charass_value INTO _value
  FROM charass, char
  WHERE ( (charass_char_id=char_id)
   AND (charass_target_type=pType )
   AND (charass_target_id=pItemid)
   AND (char_name=pCharName) );
  IF (NOT FOUND) THEN
    _value = '';
  END IF;

  RETURN _value;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION charass_getvalue(text,integer, text)
  OWNER TO admin;


-- Function: itemcharvalue(integer, text)

-- DROP FUNCTION itemcharvalue(integer, text);

CREATE OR REPLACE FUNCTION charass_setvalue(text, integer, text, text)
  RETURNS text AS
$BODY$
-- Copyright (c) 1999-2011 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pType ALIAS FOR $1;
  pItemid ALIAS FOR $2;
  pCharName ALIAS FOR $3;
  i_value ALIAS FOR $4;
  _value TEXT;
  v_char_id INTEGER;
  v_charass_id INTEGER;
  
BEGIN

  SELECT
       charass_id
        INTO
        v_charass_id
     FROM charass, char
        WHERE ( (charass_char_id=char_id)
         AND (charass_target_type=pType )
         AND (charass_target_id=pItemid)
         AND (char_name=pCharName)
         -- only works for these types...
        AND pType IN ('C', 'I')
         );
         
    IF (NOT FOUND) THEN
        -- no type validation here...
        
        SELECT char_id INTO v_char_id
            FROM
                char
            WHERE
                char_name = pCharName
                AND
                -- allowed types....
                pType IN ('C', 'I')
            LIMIT 1;
        
        IF (NOT FOUND) THEN
            RAISE EXCEPTION 'Invalid char name %', pCharName;
        END IF;

        INSERT INTO
            charass (
                charass_char_id, charass_target_type ,
                charass_target_id, charass_value
            )
            VALUES
            (
                v_char_id , pType,
                pItemid, i_value 
            );
        RETURN i_value;
    END IF;

    UPDATE charass SET charass_value = i_value WHERE charass_id = v_charass_id;
    RETURN i_value;
    
   
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION charass_setvalue(text,integer, text, text)
  OWNER TO admin;
