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

Collapse
Posted by MaineBob OConnor on
Ok, I'm new to plpgsql, but I see that it is really cool, and potentally FAST and therefore it will be really useful to me. I haven't quite got the syntax right. Here is the code I plan to use in my helper table that stores the latest message in each thread.
Create table bboard_latest (
        root_msg_id      char(6) not null,
        msg_id           char(6) not null
);

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;

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 temp x 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_lastest
    SET msg_id = vmsg_id
    WHERE root_msg_id = vroot_msg_id;
  END IF;
  RETURN ''t'';
END;
' LANGUAGE 'plpgsql';

-- Try it
select get_last_bbthread('000400','000401');
ERROR:  parser: parse error at or near "x"

I have tried variations to get it right and I don't understand the necessity of "select into" vs just a select. I am further confused by the PG docs which say:

    Note: CREATE TABLE AS is functionally equivalent to SELECT INTO. CREATE TABLE AS is the recommended syntax, since SELECT INTO is not standard. In fact, this form of SELECT INTO is not available in PL/pgSQL or ecpg, because they interpret the INTO clause differently.

Ok, I would enjoy a bit of help and explaination.
THank you.
-Bob