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