Thread: Correctly quoting inside plpgsql functions

Hi all.
I'm trying to create a function which should return a cursor (to be used
from a jdbc connection) and I'm experiencing problems in quoting (I believe)
correctly inside the function:

<code>
jsg_2=# CREATE FUNCTION myfun(char(2)) RETURNS refcursor AS
jsg_2-# ' DECLARE
jsg_2'# l_lang alias for $1;
jsg_2'# l_cursor refcursor;
jsg_2'# BEGIN
jsg_2'# OPEN l_cursor FOR EXECUTE
jsg_2'# ''SELECT * FROM t INNER JOIN i18n ON t.id=i18n.id AND
i18n.language_id='' || quote_ident(l_lang) || '' ORDER BY description'';
jsg_2'# RETURN l_cursor;
jsg_2'# END;
jsg_2'# ' LANGUAGE 'plpgsql';
CREATE
jsg_2=# select myfun('en',0);
NOTICE: Error occurred while executing PL/pgSQL function myfun
NOTICE: line 5 at open
ERROR: Attribute 'en' not found
</code>

The Function intially gets parsed, but when called with the parameter, it
seems that no quotes are around the first variable.

What am I missing?

alberto.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try quote_literal instead.

Joshua b. Jore
http://www.greentechnologist.org

On Sat, 20 Apr 2002, alberto bolchini wrote:

> jsg_2'# ''SELECT * FROM t INNER JOIN i18n ON t.id=i18n.id AND
> i18n.language_id='' || quote_ident(l_lang) || '' ORDER BY description'';

> jsg_2=# select myfun('en',0);
> NOTICE: Error occurred while executing PL/pgSQL function myfun
> NOTICE: line 5 at open
> ERROR: Attribute 'en' not found
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (OpenBSD)
Comment: For info see http://www.gnupg.org

iD8DBQE8wXBBfexLsowstzcRArkjAKDJHOgbVyGUz9YYXwX6VJoR23k8YACfVxeO
lJ74TlFfnPByZ+Bhfhi9mUw=
=1Y0e
-----END PGP SIGNATURE-----

Yup! thanks a lot Joshua, it works.
I've tried a few combinations of a number of qoutes, with an without the
quote_ident/quote_literal before writing to the list, but actually the
problem was that when I was correctly quoting, I misunderstood the error
I got upon FETCHing the rows:
ERROR:  parser: parse error at or near "'"
it was not due to the incorrect quoting but to the incorrect FETCH I was
issueing:
jsg_2=# FETCH ALL IN "<unnamed cursor 1>";
instead of
jsg_2=# FETCH ALL IN "<unnamed cursor 1>";

Thanx.
a.

Joshua b. Jore wrote:

>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>Try quote_literal instead.
>
>Joshua b. Jore
>http://www.greentechnologist.org
>
>On Sat, 20 Apr 2002, alberto bolchini wrote:
>
>
>>jsg_2'# ''SELECT * FROM t INNER JOIN i18n ON t.id=i18n.id AND
>>i18n.language_id='' || quote_ident(l_lang) || '' ORDER BY description'';
>>
>
>>jsg_2=# select myfun('en',0);
>>NOTICE: Error occurred while executing PL/pgSQL function myfun
>>NOTICE: line 5 at open
>>ERROR: Attribute 'en' not found
>>
>-----BEGIN PGP SIGNATURE-----
>Version: GnuPG v1.0.6 (OpenBSD)
>Comment: For info see http://www.gnupg.org
>
>iD8DBQE8wXBBfexLsowstzcRArkjAKDJHOgbVyGUz9YYXwX6VJoR23k8YACfVxeO
>lJ74TlFfnPByZ+Bhfhi9mUw=
>=1Y0e
>-----END PGP SIGNATURE-----
>
>
>
>