Thread: problem with plpgsql function

Hi,

I have a table "isbn" (postgres-7.0.2) with two columns:

   book_id int4
   isbn varchar(20)

I want to create a function that joins all isbn for specified book_id.
The code of this function is:

   CREATE FUNCTION all_isbn2( int4 ) RETURNS varchar AS 'DECLARE
   i isbn%ROWTYPE;
   res varchar;
   BEGIN res := "";
   FOR i IN SELECT * FROM isbn WHERE isbn.book_id = $1 LOOP
   res := res || i.isbn;
   END LOOP;
   RETURN res;
   END;' LANGUAGE 'plpgsql';

But when i execute the query

book=> select all_isbn2(1);

I receive the following error:

ERROR:  Attribute '' not found

What I'm doing wrong?

Regards,
Alex

Alex Guryanow <gav@nlr.ru> writes:
>    CREATE FUNCTION all_isbn2( int4 ) RETURNS varchar AS 'DECLARE
>    i isbn%ROWTYPE;
>    res varchar;
>    BEGIN res := "";
>    FOR i IN SELECT * FROM isbn WHERE isbn.book_id = $1 LOOP
>    res := res || i.isbn;
>    END LOOP;
>    RETURN res;
>    END;' LANGUAGE 'plpgsql';

> ERROR:  Attribute '' not found

> What I'm doing wrong?

You need to write
    BEGIN res := '''';
Double quotes "" imply a variable or column name, not a string literal.
You need 4 quotes not 2 because you're inside a ' literal already
(you could also write res := \'\' if that seems clearer).

It occurs to me that we ought to make a push to consistently use
double-quotes not single-quotes in error messages that are reporting
names.  If the error had been
    ERROR:  Attribute "" not found
you might've figured out your mistake without help...

            regards, tom lane