Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2009
    Posts
    37

    Unanswered: Function with output parameter

    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;".

    Code:
    CREATE FUNCTION sp_checksendaccounteventnotification(accountid int8, OUT returnvalue int8)
    AS
    $$
    DECLARE 
    latestlogon timestamp without time zone;
    latestcriticalevent timestamp without time zone;
    
    BEGIN
    
    SELECT 
    	latestlogon = MAX("LastLogon")
    FROM
    	public."tblUser"
    WHERE
    	"AccountID" = $1 AND
    	"AccountAdmin" = TRUE AND 
    	"AccountEventNotify" = TRUE AND
    	"DateDeleted" IS NOT NULL;
    
    
    SELECT 
    	latestcriticalevent = MAX(DateCreated)
    FROM
    	public."tblAccountEvent"
    WHERE
    	"AccountID" = $1 AND
    	"Critical" = TRUE;
    
    IF latestcriticalevent IS NULL OR (latestcriticalevent  <= latestlogon) THEN
    	returnvalue := 1; -- send notification
    ELSE
    	returnvalue := 0; -- don't send
    END;
    $$
    LANGUAGE 'plpgsql' VOLATILE;
    Thanks in advance.

  2. #2
    Join Date
    May 2008
    Posts
    277

    Thumbs up

    You need to close your IF statement; your END is closing the entire procedure.

    Code:
        ...
        IF latestcriticalevent IS NULL OR (latestcriticalevent <= latestlogon) THEN
            returnvalue := 1; -- send notification
        ELSE
            returnvalue := 0; -- don't send
        END IF;
    END;
    $$
    LANGUAGE 'plpgsql' VOLATILE;
    Your SELECT statements are also wrong:

    Code:
        SELECT MAX("LastLogon")
        INTO latestlogon
        FROM public."tblUser"
        ...

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    > 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:
    Code:
    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???

    Code:
    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;

  4. #4
    Join Date
    Oct 2009
    Posts
    37
    Thanks for the replies guys really appreciate it.
    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?

    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •