Hi I am trying to write a Function that uses an If statment to decide whether the output parameter will be 1 or 0. So far I have had no luck getting various syntax errors. With the below code I'm getting "Syntax error at or near END;".
CREATE FUNCTION sp_checksendaccounteventnotification(accountid int8, OUT returnvalue int8)
latestlogon timestamp without time zone;
latestcriticalevent timestamp without time zone;
latestlogon = MAX("LastLogon")
"AccountID" = $1 AND
"AccountAdmin" = TRUE AND
"AccountEventNotify" = TRUE AND
"DateDeleted" IS NOT NULL;
latestcriticalevent = MAX(DateCreated)
"AccountID" = $1 AND
"Critical" = TRUE;
IF latestcriticalevent IS NULL OR (latestcriticalevent <= latestlogon) THEN
returnvalue := 1; -- send notification
returnvalue := 0; -- don't send
LANGUAGE 'plpgsql' VOLATILE;
> So far I have had no luck getting various syntax errors.
That's because your syntax is wrong. Lots of SQL Server baggage here. You need to use something like:
SELECT MAX(lastlogon) INTO latestlogon FROM ...
But you could do this all in a single query inside of a sql function instead. And SURELY accountID is the pk for the tblUser so there would be no need for the MAX() right???
CREATE OR REPLACE FUNCTION foo(accountID INT8)
RETURNS INT AS
SELECT CASE WHEN e.evtDate IS NULL
OR e.evtDate <= usr.lastLogon THEN 1
ELSE 0 END
FROM tblUser usr
LEFT JOIN (
SELECT MAX(DateCreated) AS evtDate
FROM tblAccountEvent evt
WHERE evt.AccountID = $1
AND evt.critical = true
) e ON 1=1
WHERE usr.AccountID = $1
AND AccountAdmin = true
AND AccountEventNotify = true
AND DateDeleted IS NOT NULL;
$$ LANGUAGE 'sql' VOLATILE;
And SURELY accountID is the pk for the tblUser so there would be no need for the MAX() right???
No the accountID isn't the primary key as we have more than one user per account.
No too sure if I fully understand your function artacus, however the main problem that I have with writing a function that returns an int rather than an output parameter is that the application using the database is ADO.NET and so when the stored procedure is run through a function the output parameter is assigned to a variable. So I am not 100% sure on how to pick up the return value if it is not a parameter. Not sure if you have had any experience with ADO.NET if you have any ideas then great.
So with the function you have written instead of saying just 1 can we use the output parameter the way that I have written it?