Postgres Stored Procedure programming

[1phpbuilder]

I'm migrating a webapp from MSSQL to Postgresql and I'm having some problems with the store procedure. This is what I have so far, but it doesn't seem to be working. I'm getting an error that says

ERROR: parser: parse error at or near "INTO" at character 9

What I need to do

1.) Get the visitor_id
I want to check to see if the current cookie is associated with a visitor_id, if the visitor_id is not found, then add it and put the visitor_id into a variable, if it is found then store it in the visitor_id variable.

2.) Get the session_id
Next I need to check to see if the current session has been stored in the db, if it has been return the session_id, if not, it should be added and a session_id should be returned.

3.) Save the page view
Insert the pageview record.

---

Here's my stored procedure

CREATE OR REPLACE FUNCTION log_page_view(INTEGER,VARCHAR,VARCHAR,VARCHAR,VARCHAR) RETURNS INTEGER AS '
DECLARE
visitors record;
sessions record;
vistor_cookies record;
local_account_id ALIAS FOR $1;
local_cookie_id ALIAS FOR $2;
local_user_agent ALIAS FOR $3;
local_client_ip ALIAS FOR $4;
local_page_url ALIAS FOR $5;
BEGIN

  SELECT INTO visitor_cookies c.visitor_id FROM web_visitor_cookies c
         WHERE c.cookie_id = local_cookie_id;

  IF NOT FOUND THEN
     BEGIN
          INSERT INTO web_visitors(account_id) VALUES(local_account_id);
          SELECT INTO visitors max(v.visitor_id) FROM web_visitors v;
          INSERT INTO web_visitor_cookies(visitor_id,cookie_id) VALUES(visitors.visitor_id,local_cookie_id);
     END;
  END IF;

  SELECT INTO sessions s.session_id FROM web_visitor_sessions s
         WHERE s.visitor_id = visitors.visitor_id AND s.cookie_id=local_cookie_id;
  IF NOT FOUND THEN
     BEGIN
          INSERT INTO web_visitor_sessions(visitor_id,cookie_id,client_ip,user_agent) VALUES(visitors.visitor_id,local_cookie_id,local_client_ip,local_user_agent);
          SELECT INTO sessions max(s.session_id) FROM web_visitor_sessions s;
     END;
  END IF;

 INSERT INTO web_pageviews(session_id,page_url) VALUES(sessions.session_id,local_page_url);


RETURN visitors.visitor_id;

END;
' LANGUAGE 'plpgsql';

[chriskl]

Are you sure you're not just making up syntax here? I suggest you email [e-mail] for help.

I have no idea what that BEGIN;END; stuff you're doing is? You only can have one of them I think.

But that's probably not the problem.

Hrm - looking at it I cannot see the problem - email the help list.

Chris

[1phpbuilder]

I finally got it working. This is my first experience with plpgsql, thanks for the tip about the mailing list!

Here's the function

DROP FUNCTION "log_page_view"(integer, character varying, character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION "log_page_view"(INTEGER,VARCHAR,VARCHAR,VARCHAR,VARCHAR) RETURNS INTEGER AS '
DECLARE
local_visitor_id integer;
local_session_id integer;
local_account_id ALIAS FOR $1;
local_cookie_id ALIAS FOR $2;
local_user_agent ALIAS FOR $3;
local_client_ip ALIAS FOR $4;
local_page_url ALIAS FOR $5;
BEGIN

  SELECT INTO local_visitor_id visitor_id FROM web_visitor_cookies WHERE web_visitor_cookies.cookie_id=local_cookie_id;
  IF NOT FOUND THEN
     BEGIN
  	     local_visitor_id := nextval(\'web_visitors_visitor_id_seq\');
          INSERT INTO web_visitor_cookies(visitor_id,cookie_id) VALUES(local_visitor_id,local_cookie_id);
          INSERT INTO web_visitors(visitor_id,account_id) VALUES(local_visitor_id,local_account_id);
     END;
  END IF;

  SELECT INTO local_session_id session_id FROM web_visitor_sessions
         WHERE web_visitor_sessions.visitor_id=local_visitor_id AND web_visitor_sessions.cookie_id=local_cookie_id;
  IF NOT FOUND THEN
     BEGIN
	 	local_session_id := nextval(\'web_visitor_sessions_session_id_seq\');
          INSERT INTO web_visitor_sessions(session_id,visitor_id,cookie_id,client_ip,user_agent) VALUES(local_session_id,local_visitor_id,local_cookie_id,local_client_ip,local_user_agent);
     END;
  END IF;

  INSERT INTO web_pageviews(session_id,page_url) VALUES(local_session_id,local_page_url);

  local_visitor_id := local_visitor_id;

RETURN local_visitor_id;

END;
' LANGUAGE 'plpgsql';