#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    58
    Rep Power
    11

    My First plpgsql Function fails


    I installed plpgsql support on a pgsql database.

    I created a test function like this:

    CREATE FUNCTION Hello(VARCHAR) RETURNS VARCHAR AS '
    DECLARE
    pName ALIAS FOR $1;
    BEGIN
    RETURN "Hello " || pName;
    END;
    ' LANGUAGE 'plpgsql';

    I create function from psql, and function is created.

    When I try to call function from a select statement such as:

    SELECT Hello(Name)
    FROM tblData;

    An error occurs:

    Invalid attribute "Hello " on function Hello.

    I don't know what is happening.

    I appreciate some help

    Best Regards
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    You need to replace the double quotes with single quotes in your function:

    RETURN 'Hello ' || pName;

    Double quotes are for attributes (fields, tables) single quotes for string literals.
  4. #3
  5. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    One small oversight:

    Since the function definition is already inside single quotes, you need to escape your string literals with two single quotes:

    Code:
    CREATE OR REPLACE FUNCTION Hello(VARCHAR) RETURNS VARCHAR AS '
    DECLARE
    pName ALIAS FOR $1;
    BEGIN
    RETURN ''Hello '' || pName;
    END;
    ' LANGUAGE 'plpgsql';
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  6. #4
  7. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    Oooops!
  8. #5
  9. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Originally posted by rod k
    Oooops!
    lol... yes, the double-single-quote thing is one of the primary complaints made against the PostgreSQL procedural language system. If you start nesting logic in functions, such as functions that create functions, or evaluate strings for logic, you can end up with lots of single quotes.

    It seems that there are no plans to change this at the present, though, so I guess we will have to get used to it...
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo