MSSQL -> Postgres stored procedure migration/plpgsql help?

[AndyHopper]

I'm migrated 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.

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.

---------------------

CREATE FUNCTION log_page_view(int,varchar,varchar,varchar,varchar) RETURNS INTEGER AS '
DECLARE
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;
local_visitor_id int := 0;
local_session_id int := 0;
BEGIN

SELECT INTO local_visitor_id visitor_id FROM web_visitors WHERE cookie_id = local_cookie_id;
IF NOT FOUND THEN
INSERT INTO web_visitors(account_id) VALUES(1);
SELECT INTO local_visitor_id max(visitor_id) FROM web_visitors;
END IF;

SELECT INTO local_session_id visitor_id FROM web_visitor_sessions WHERE visitor_id = local_cookie_id AND cookie_id=local_cookie_id;
IF NOT FOUND THEN
INSERT INTO web_visitor_sessions(visitor_id,cookie_id,client_ip,user_agent) VALUES(local_visitor_id,local_cookie_id,local_client_ip,local_user_agent);
SELECT INTO local_session_id session_id FROM max(session_id) FROM web_visitor_sessions;
END IF;

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

RETURN local_visitor_id;
END;
' LANGUAGE 'plpgsql';

 

[AndyHopper]

I worked some of the kinks out and got this function to run, however it doesn't insert any data :-(.

CREATE OR REPLACE FUNCTION log_page_view(INTEGER,VARCHAR,VARCHAR,VARCHAR,VARCHAR) RETURNS INTEGER AS '
DECLARE
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;
local_visitor_id int := 0;
local_session_id int := 0;
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
INSERT INTO web_visitors(account_id) VALUES(1);
GET DIAGNOSTICS local_visitor_id = RESULT_OID;
END IF;

SELECT INTO local_session_id session_id FROM web_visitor_sessions WHERE web_visitor_sessions.visitor_id = local_cookie_id AND cookie_id=local_cookie_id;
IF NOT FOUND THEN
INSERT INTO web_visitor_sessions(visitor_id,cookie_id,client_ip,user_agent) VALUES(local_visitor_id,local_cookie_id,local_client_ip,local_user_agent);
GET DIAGNOSTICS local_session_id = RESULT_OID;
END IF;

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

RETURN local_visitor_id;
END;
' LANGUAGE 'plpgsql';

 

[newbiepg]

tell us what the error is

the only issue I can figure out here

It may be better to declare the first parameter as int4.
numbers default to int4. If this is the problem then you will get an error like "unrecognized function log_page_view(int4,varchar,varchar,varchar,varchar) "