Results 1 to 6 of 6
  1. #1
    Join Date
    May 2009
    Posts
    6

    Unanswered: trouble with create function

    hello to everybody, I am trying to create a simple function for Postgresql 8.3
    here my code:
    ---------------------------------
    CREATE FUNCTION progression_job(IN data1 date, OUT date_for date, INOUT repetition integer)
    RETURNS record AS'
    DECLARE
    date data1;
    date_for date;
    repetition integer;
    BEGIN

    IF repetition=0 THEN
    date_for:= d + integer `1`;
    ELSIF repetition=1 THEN
    date_for:=d::timestamp + `3 days`::interval;
    END IF;
    IF repetition < 4 THEN
    repetition:=repetition +1;
    END IF;
    return date_for;
    end;'
    ------------------------------
    Pgadmin3 return me this message
    ERROR: syntax error at or near "data1"
    LINE 4: date data1;
    ___________^


    ********** Error **********

    ERROR: syntax error at or near "data1"
    SQL state: 42601
    Character: 137

    I do not know where is my mistake can somebody help me?
    thanks in advance

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    data1 is not a datatype and why do you declare the parameters again?

  3. #3
    Join Date
    May 2009
    Posts
    6
    thanks, I removed the declare section

    now my code is:
    --------------------
    CREATE FUNCTION progression_job(IN data1 date, OUT date_for date, INOUT repetition integer)
    RETURNS record AS'

    BEGIN

    IF repetition=0 THEN
    date_for:= d + integer `1`;
    ELSIF repetition=1 THEN
    date_for:=d::timestamp + `3 days`::interval;
    END IF;
    IF repetition < 4 THEN
    repetition:=repetition +1;
    END IF;
    return date_for;
    end;'



    LANGUAGE 'sql';
    ------------------------
    but the message error now is:

    ERROR: syntax error at or near "IF"
    LINE 6: IF repetition=0 THEN
    ^


    ********** Error **********

    ERROR: syntax error at or near "IF"
    SQL state: 42601
    Character: 124
    what is wrong whit the IF statement?

  4. #4
    Join Date
    May 2009
    Posts
    6
    thanks, this is the first function I wrote for postgresql, I removed the declare section, now my code is:
    -----------------------
    CREATE FUNCTION progression_job(IN data1 date, OUT date_for date, INOUT repetition integer)
    RETURNS record AS'

    BEGIN

    IF repetition=0 THEN
    date_for:= d + integer `1`;
    ELSIF repetition=1 THEN
    date_for:=d::timestamp + `3 days`::interval;
    END IF;
    IF repetition < 4 THEN
    repetition:=repetition +1;
    END IF;
    return date_for;
    end;'



    LANGUAGE 'sql';

    -------------------------
    but now i got this message of error:
    -----------------------
    ERROR: syntax error at or near "IF"
    LINE 6: IF repetition=0 THEN
    ^


    ********** Error **********

    ERROR: syntax error at or near "IF"
    SQL state: 42601
    Character: 124
    -------------------------
    what is it wrong whit the IF statement?

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by arpho
    what is it wrong whit the IF statement?
    There is no IF in SQL. You need to use LANGUAGE plpgsql

    Did you check out the examples in the manual: PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: Declarations

  6. #6
    Join Date
    May 2009
    Posts
    6
    Hi, thanks for your reply, I have changed language, how you told me, then I got this message:

    ERROR: language "plpgsql" does not exist
    HINT: Use CREATE LANGUAGE to load the language into the database.

    ********** Error **********

    ERROR: language "plpgsql" does not exist
    SQL state: 42704
    Hint: Use CREATE LANGUAGE to load the language into the database.



    ---------------------------------------
    thus I add CREATE LANGUAGE plpgsql;
    then the error message became:

    ERROR: syntax error at or near "CREATE"
    LINE 19: CREATE LANGUAGE plpgsql;
    ^


    ********** Error **********

    ERROR: syntax error at or near "CREATE"
    SQL state: 42601
    Character: 413

Posting Permissions

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