Re: plpgsql function, comment with single quote, braces

I don't know the details of the escape syntax parsing. You should look
at the pgjdbc source code if you need to understand.

Otherwise what's the problem if you just deactivate escape processing?

Florent

2012/1/20 Роман Литовченко <roman(dot)lytovchenko(at)gmail(dot)com>:
> Hi,
>
> I think that in general your words are not truth.
>
> package org.postgresql.jdbc.braces;
>
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.SQLException;
> import java.sql.Statement;
>
> public class SingleQuote {
>
>        /**
>         * @param args
>         * @throws ClassNotFoundException
>         */
>        public static void main(String[] args) {
>                // TODO Auto-generated method stub
>
>                try {
>                        Connection dbCon =
> DriverManager.getConnection("jdbc:postgresql://127.0.0.1/b", "b",
> "b");
>
>                Statement statement = dbCon.createStatement();
>
>
>                //statement.setEscapeProcessing(false);
>
>                statement.execute("create or replace function f () returns
> void as $f$ begin --  '   comment with single quote symbol" +
>                                "\n" +
>                                "declare _D_68 text := '{D}'; begin end; end; $f$ language
> plpgsql;");
>
>                statement.execute("create or replace function f2 () returns
> void as $f$ begin --  why here is no escape syntax?" +
>                                "\n" +
>                                "declare _D_68 text := '{D}'; begin end; end; $f$ language
> plpgsql;");
>
>                statement.execute("create or replace function f3 () returns
> void as $$ begin --  and why here is no escape syntax?" +
>                                "\n" +
>                                "declare _D_68 text := '{D}'; begin end; end; $$ language plpgsql;");
>
>                dbCon.close();
>
>                } catch (SQLException e) {
>                        // TODO Auto-generated catch block
>                        e.printStackTrace();
>                }
>
>        }
>
> }
>
> and traditionally:
> select proname, prosrc
> from pg_proc
> where proname in ('f', 'f2', 'f3');
>
>
> f; begin --  '   comment with single quote symbol
> declare _D_68 text := 'DATE '; begin end; end;
> f2; begin --  why here is no escape syntax?
> declare _D_68 text := '{D}'; begin end; end;
> f3; begin --  and why here is no escape syntax?
> declare _D_68 text := '{D}'; begin end; end;
>
> What about these?
>
>
>
>
> 20 січня 2012 р. 12:10 Florent Guillaume <fg(at)nuxeo(dot)com> написав:
>> Hi,
>>
>> What you're seeing is due to the SQL escape syntax for literals in Statements.
>> http://docs.oracle.com/javase/1.4.2/docs/guide/jdbc/getstart/statement.html#999472
>>
>> You should call statement.setEscapeProcessing(false) if you don't want
>> that to happen.
>>
>> Florent
>>
>>
>> 2012/1/19 Роман Литовченко <roman(dot)lytovchenko(at)gmail(dot)com>:
>>> ---------- Переслане повідомлення ----------
>>> Від: Роман Литовченко <roman(dot)lytovchenko(at)gmail(dot)com>
>>> Дата: 19 січня 2012 р. 11:02
>>> Тема: Re: [JDBC] plpgsql function, comment with single quote, braces
>>> Кому: David Johnston <polobo(at)yahoo(dot)com>
>>>
>>>
>>> well, I will try to explain it from other side. )
>>>
>>> I have some local server
>>> PostgreSQL 9.1.1 on x86_64-apple-darwin10.8.0, compiled by
>>> i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5666) (dot
>>> 3), 64-bit
>>>
>>> I wrote some code for you:
>>>
>>> package org.postgresql.jdbc.braces;
>>>
>>> import java.sql.Connection;
>>> import java.sql.DriverManager;
>>> import java.sql.SQLException;
>>> import java.sql.Statement;
>>>
>>> public class SingleQuote {
>>>
>>>        /**
>>>         * @param args
>>>         * @throws ClassNotFoundException
>>>         */
>>>        public static void main(String[] args) {
>>>                // TODO Auto-generated method stub
>>>
>>>                try {
>>>                        Connection dbCon =
>>> DriverManager.getConnection("jdbc:postgresql://127.0.0.1/b", "b",
>>> "b");
>>>                Statement statement = dbCon.createStatement();
>>>                statement.execute("create or replace function f () returns
>>> void as $f$ begin --  '   comment with single quote symbol" +
>>>                                "\n" +
>>>                                "declare _D_68 text := '{D}'; begin
>>> end; end; $f$ language
>>> plpgsql;");
>>>
>>>                dbCon.close();
>>>
>>>                } catch (SQLException e) {
>>>                        // TODO Auto-generated catch block
>>>                        e.printStackTrace();
>>>                }
>>>
>>>        }
>>>
>>> }
>>>
>>> You need attach postgresql-9.1-901.jdbc4.jar and run this code.
>>>
>>> Then you need to run the query using any tools:
>>>
>>> select proname, prosrc
>>> from pg_proc
>>> where proname = 'f';
>>>
>>> result is
>>>
>>> f; begin --  '   comment with single quote symbol
>>> declare _D_68 text := 'DATE '; begin end; end;
>>>
>>> Pay attention for value of _D_68.
>>>
>>> So, please, check it.
>>>
>>> P.S.
>>> don't warry about <pre>
>>> don't warry about link to forum - for you that post contains only function body.
>>>
>>>
>>> 2012/1/19 David Johnston <polobo(at)yahoo(dot)com>:
>>>> -----Original Message-----
>>>> From: pgsql-jdbc-owner(at)postgresql(dot)org
>>>> [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of ????? ??????????
>>>> Sent: Wednesday, January 18, 2012 4:20 PM
>>>> To: pgsql-jdbc(at)postgresql(dot)org
>>>> Subject: [JDBC] plpgsql function, comment with single quote, braces
>>>>
>>>> This email repeats my post
>>>> http://www.sql.ru/forum/actualthread.aspx?tid=908777
>>>>
>>>> I used
>>>> PostgreSQL 9.1.1 on x86_64-apple-darwin10.8.0, compiled by
>>>> i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5666) (dot 3),
>>>> 64-bit PostgreSQL 8.4.6 on i386-apple-darwin, compiled by GCC
>>>> i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370),
>>>> 32-bit with postgresql-8.4-701.jdbc3.jar postgresql-8.4-701.jdbc4.jar
>>>> postgresql-9.1-901.jdbc3.jar postgresql-9.1-901.jdbc4.jar
>>>>
>>>> for creating function like this:
>>>>
>>>> <pre>
>>>> create or replace function f ()
>>>> returns void as $f$ begin
>>>> --  '   comment with single quote symbol
>>>> declare
>>>>  _A_65 text := '{A}';
>>>>  _B_66 text := '{B}';
>>>>  _C_67 text := '{C}';
>>>>  _D_68 text := '{D}';
>>>>  _E_69 text := '{E}';
>>>>  _F_70 text := '{F}';
>>>>  _G_71 text := '{G}';
>>>>  _H_72 text := '{H}';
>>>>  _I_73 text := '{I}';
>>>>  _J_74 text := '{J}';
>>>>  _K_75 text := '{K}';
>>>>  _L_76 text := '{L}';
>>>>  _M_77 text := '{M}';
>>>>  _N_78 text := '{N}';
>>>>  _O_79 text := '{O}';
>>>>  _P_80 text := '{P}';
>>>>  _Q_81 text := '{Q}';
>>>>  _R_82 text := '{R}';
>>>>  _S_83 text := '{S}';
>>>>  _T_84 text := '{T}';
>>>>  _U_85 text := '{U}';
>>>>  _V_86 text := '{V}';
>>>>  _W_87 text := '{W}';
>>>>  _X_88 text := '{X}';
>>>>  _Y_89 text := '{Y}';
>>>>  _Z_90 text := '{Z}';
>>>>  _a_97 text := '{a}';
>>>>  _b_98 text := '{b}';
>>>>  _c_99 text := '{c}';
>>>>  _d_100 text := '{d}';
>>>>  _e_101 text := '{e}';
>>>>  _f_102 text := '{f}';
>>>>  _g_103 text := '{g}';
>>>>  _h_104 text := '{h}';
>>>>  _i_105 text := '{i}';
>>>>  _j_106 text := '{j}';
>>>>  _k_107 text := '{k}';
>>>>  _l_108 text := '{l}';
>>>>  _m_109 text := '{m}';
>>>>  _n_110 text := '{n}';
>>>>  _o_111 text := '{o}';
>>>>  _p_112 text := '{p}';
>>>>  _q_113 text := '{q}';
>>>>  _r_114 text := '{r}';
>>>>  _s_115 text := '{s}';
>>>>  _t_116 text := '{t}';
>>>>  _u_117 text := '{u}';
>>>>  _v_118 text := '{v}';
>>>>  _w_119 text := '{w}';
>>>>  _x_120 text := '{x}';
>>>>  _y_121 text := '{y}';
>>>>  _z_122 text := '{z}';
>>>> begin
>>>> end;
>>>>
>>>> end; $f$ language plpgsql;
>>>> </pre>
>>>>
>>>> and get this in my database:
>>>>
>>>> <pre>
>>>> ...
>>>> CREATE OR REPLACE FUNCTION f()
>>>>  RETURNS void AS
>>>> $BODY$ begin
>>>> --  '   comment
>>>> declare
>>>>  _A_65 text := '{A}';
>>>>  _B_66 text := '{B}';
>>>>  _C_67 text := '{C}';
>>>>  _D_68 text := 'DATE ';
>>>>  _E_69 text := 'E';
>>>>  _F_70 text := '';
>>>>  _G_71 text := '{G';
>>>>  _H_72 text := '{H}';
>>>>  _I_73 text := '{I}';
>>>>  _J_74 text := '{J}';
>>>>  _K_75 text := '{K}';
>>>>  _L_76 text := '{L}';
>>>>  _M_77 text := '{M}';
>>>>  _N_78 text := '{N}';
>>>>  _O_79 text := '';
>>>>  _P_80 text := '{P}';
>>>>  _Q_81 text := '{Q}';
>>>>  _R_82 text := '{R}';
>>>>  _S_83 text := '{S}';
>>>>  _T_84 text := 'TIME ';
>>>>  _U_85 text := '{U}';
>>>>  _V_86 text := '{V}';
>>>>  _W_87 text := '{W}';
>>>>  _X_88 text := '{X}';
>>>>  _Y_89 text := '{Y}';
>>>>  _Z_90 text := '{Z}';
>>>>  _a_97 text := '{a}';
>>>>  _b_98 text := '{b}';
>>>>  _c_99 text := '{c}';
>>>>  _d_100 text := 'DATE ';
>>>>  _e_101 text := 'e';
>>>>  _f_102 text := '';
>>>>  _g_103 text := '{g';
>>>>  _h_104 text := '{h}';
>>>>  _i_105 text := '{i}';
>>>>  _j_106 text := '{j}';
>>>>  _k_107 text := '{k}';
>>>>  _l_108 text := '{l}';
>>>>  _m_109 text := '{m}';
>>>>  _n_110 text := '{n}';
>>>>  _o_111 text := '';
>>>>  _p_112 text := '{p}';
>>>>  _q_113 text := '{q}';
>>>>  _r_114 text := '{r}';
>>>>  _s_115 text := '{s}';
>>>>  _t_116 text := 'TIME ';
>>>>  _u_117 text := '{u}';
>>>>  _v_118 text := '{v}';
>>>>  _w_119 text := '{w}';
>>>>  _x_120 text := '{x}';
>>>>  _y_121 text := '{y}';
>>>>  _z_122 text := '{z}';
>>>> begin
>>>> end;
>>>>
>>>> end; $BODY$
>>>>  LANGUAGE plpgsql VOLATILE
>>>>  COST 100;
>>>> ...
>>>> </pre>
>>>>
>>>> please check it.
>>>>
>>>> ----------------------------------------------------------------------------
>>>> --------------
>>>>
>>>> Check What?
>>>>
>>>> You do not provide enough information in this posting and while you do link
>>>> to a more detailed posting online it is in Russian whereas you are
>>>> apparently asking a question that you expect an English speaking audience to
>>>> be able to answer.
>>>>
>>>> How are you executing your CREATE FUNCTION statement?
>>>> What are you using to see what is in the database?
>>>> Why are you including "<pre>" within a text e-mail?
>>>>
>>>> Since you bring it up if the linked post receives an answer it would be nice
>>>> if you could provide a quick translation of what is/was found in order to
>>>> wrap up / close this listing.
>>>>
>>>> David J.
>>>>
>>>>
>>>
>>> --
>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>
>>
>>
>> --
>> Florent Guillaume, Director of R&D, Nuxeo
>> Open Source, Java EE based, Enterprise Content Management (ECM)
>> http://www.nuxeo.com   http://www.nuxeo.org   +33 1 40 33 79 87

--
Florent Guillaume, Director of R&D, Nuxeo
Open Source, Java EE based, Enterprise Content Management (ECM)
http://www.nuxeo.com   http://www.nuxeo.org   +33 1 40 33 79 87