AustinTech

Been a while since I posted, but here’s a nice tip that I had to figure out the hard way.

Problem - Using a function in INOUT and OUT parameters to pass information back to another function. This is function I needed to return data from.

 CREATE OR REPLACE FUNCTION vx_string.get_next_token(p_delimiter IN varchar,
      p_text INOUT varchar, p_return OUT varchar) RETURNS RECORD AS $BODY$
     DECLARE
          v_position   INTEGER;
    BEGIN
        -- Find position of delimiter.
        v_position := vx_utils.instr(p_text,p_delimiter, 1);
       IF v_position > 0 THEN
           p_return := SUBSTR(p_text, 1, v_position - 1);
           p_text := SUBSTR(p_text, v_position + LENGTH(p_delimiter));
       ELSE
           p_return := p_text;
           p_text := '';
       END IF;
       RETURN p_text, p_return;
    END;
    $BODY$
    LANGUAGE 'plpgsql' VOLATILE;

Solution - In order to return the values I found I needed to SELECT the values INTO specific vars using the SELECT * INTO …. syntax as follows:

    DECLARE
        v_line TEXT;
        v_temp TEXT;
    BEGIN
       -- notice we only pass in the first two vars since they
       -- are IN and INOUT respectfully. They OUT parameter is only used
       -- in the returning record.
       SELECT *
       INTO v_line, v_temp
       FROM
       vx_string.get_next_token(';','0001; My Test Line; My Test Line; My Test Line');
       -- at this point, v_line equals "My Test Line; My Test Line; My Test Line"
       -- and v_temp equals "0001"
       -- Now, I can use v_line and v_temp within this procedure
       v_line_nbr = TO_NUMBER(v_temp,'99999');