Forum OpenACS Q&A: Response to Slow SubSelect in BBoard Forum

Collapse
Posted by MaineBob OConnor on

Ok, Thank $your_deity for Google!

I found the solution with a couple of Google serches.
I had not defined "record".
So it is defined as rec and now works! Here's the good code:

DROP FUNCTION get_last_bbthread(char,char);
CREATE FUNCTION get_last_bbthread(char(6), char(6))
RETURNS boolean

AS 'DECLARE
vroot_msg_id ALIAS FOR $1;
vmsg_id ALIAS FOR $2;
rec record;

BEGIN
IF length(vroot_msg_id) = 0 -- no root id, failure
   THEN RETURN ''f'';
END IF;

IF length(vmsg_id) = 0 -- no msg id, failure
   THEN RETURN ''f'';
END IF;

  SELECT into rec * from bboard_latest 
    WHERE root_msg_id = vroot_msg_id;
  IF NOT FOUND -- is root not in table?
  THEN INSERT INTO bboard_latest (root_msg_id,msg_id)
    VALUES (vroot_msg_id, vmsg_id);
  ELSE UPDATE bboard_latest
    SET msg_id = vmsg_id
    WHERE root_msg_id = vroot_msg_id;
  END IF;
  RETURN ''t'';
END;
' LANGUAGE 'plpgsql';

-Bob