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

    Unanswered: SQL function with DECLARE syntax error

    Hi guys. I am trying to write a function that has a DECLARE variable in it however when I go to create the function I am getting a syntax error for the DECLARE statement. Below is the code for the function. Thanks.

    CREATE OR REPLACE FUNCTION user_getschemes(userid int8)

    RETURNS TABLE(AccountID integer, SchemeID integer, SchemeName text)
    AS
    $$
    DECLARE AccountID integer;

    SELECT
    AccountID = "tblUser"."AccountID"
    FROM
    public."tblUser"
    WHERE
    "tblUser"."UserID" = $1

    SELECT DISTINCT
    "tblScheme"."SchemeID",
    "tblScheme"."SchemeName"

    FROM
    public."tblScheme"
    INNER JOIN public."tblSchemeProject" ON "tblScheme"."SchemeID" = "tblSchemeProject"."SchemeID"
    INNER JOIN public."tblSchemeProjectAccount" ON "tblSchemeProject"."SchemeProjectID" = "tblSchemeProjectAccount"."SchemeProjectID" LEFT OUTER JOIN
    public."Scheme_tblProjectUser" ON "tblSchemeProject"."SchemeProjectID" = "Scheme_tblProjectUser"."SchemeProjectID" AND "Scheme_tblProjectUser"."UserID" = $1

    WHERE
    "tblSchemeProjectAccount"."AccountID" = AccountID
    AND ("tblSchemeProjectAccount"."RestrictedUsers" = 0 OR ("tblSchemeProjectAccount"."RestrictedUsers"=1 AND "Scheme_tblProjectUser"."UserID" IS NOT NULL))


    ORDER BY
    "tblScheme"."SchemeName";
    $$LANGUAGE 'sql' STABLE;

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    DECLARE is a PL/pgSQL keyword, you need either use PL/pgSQL or remove the declare (and thus your variable).

    You have two options:

    1) Get rid of the variable by simply joining the user table with your "main" statement.

    2) Use the proper language that supports variables: plpgsql (so you need to change LANGUAGE 'sql' to LANGUAGE 'plpgsql'

    And please use [code] tags the next time you post SQL code

  3. #3
    Join Date
    Oct 2009
    Posts
    37
    Hi shammat thanks for the quick reply. That worked using plpgsql however now it is having an error saying type "accountid" does not exist. Is there a different way that I have to called the "AccountID" that was declared other than just referring to it as "AccountID"? Thanks.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    If you only changed 'sql' to 'plsql' then you are missing a semicolon after the first select and the BEGIN and END that is required by PL/pgSQL.
    Check out this: http://www.postgresql.org/docs/curre...structure.html


    Btw: I would highly recommend to get rid of the double quotes for table and column names
    Also do not give a variable the same name as a column in one of your tables. That just makes the code harder to understand.
    Last edited by shammat; 10-30-09 at 04:50. Reason: Noticed the missing BEGIN

  5. #5
    Join Date
    Oct 2009
    Posts
    37
    Hi thanks again. I made some changes and now im getting another error which says "ERROR: query has no destination for result data"
    "HINT: If you want to discard the results of a SELECT, use PERFORM instead."
    "CONTEXT: PL/pgSQL function "uesr_getschemes" line 13 at SQL statement".
    I am calling the function like this "SELECT * FROM user_getschemes(1)". The Code is below. I think that the error is to do with the first select statement. I tried changing SELECT to PERFORM but got the same error.
    Code:
    CREATE OR REPLACE FUNCTION user_getschemes(userid int8)
    
    RETURNS TABLE(SchemeID integer, SchemeName text)  
    AS
    $$
    DECLARE 
    acntid integer;
    
    BEGIN
    
    SELECT
    	acntid = "tblUser"."AccountID" 
    FROM 
    	public."tblUser"
    WHERE 
    	"tblUser"."userid" = $1;
    
    
    SELECT DISTINCT 
    	"tblScheme"."SchemeID", 
    	"tblScheme"."SchemeName"
    
    FROM 
    	public."tblScheme"
    	INNER JOIN public."tblSchemeProject" ON "tblScheme"."SchemeID" = "tblSchemeProject"."SchemeID" 
    	INNER JOIN public."tblSchemeProjectAccount" ON "tblSchemeProject"."SchemeProjectID" = "tblSchemeProjectAccount"."SchemeProjectID" LEFT OUTER JOIN
    	public."Scheme_tblProjectUser" ON "tblSchemeProject"."SchemeProjectID" = "Scheme_tblProjectUser"."SchemeProjectID" AND "Scheme_tblProjectUser"."UserID" = $1
    
    WHERE 
    	"tblSchemeProjectAccount"."AccountID" = acntid 
    	AND ("tblSchemeProjectAccount"."RestrictedUsers" = FALSE OR ("tblSchemeProjectAccount"."RestrictedUsers"= TRUE AND "Scheme_tblProjectUser"."UserID" IS NOT NULL))
    
    
    ORDER BY 
    	"tblScheme"."SchemeName";
    	RETURN QUERY;
    
    END;
    $$LANGUAGE 'plpgsql';
    Im using quotes around the column names that have uppercase characters becuase postgresql converts them to lower if you don't have quotes around them.
    Thanks
    Liam

Posting Permissions

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