FAQ
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.

Search Discussions

  • Joshua b. Jore at Apr 20, 2002 at 1:28 pm
    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
  • Alberto bolchini at Apr 20, 2002 at 2:10 pm
    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-----


Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedApr 20, '02 at 10:49a
activeApr 20, '02 at 2:10p
posts3
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase