Re: plpgsql function, comment with single quote, braces - Mailing list pgsql-jdbc

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

Because it is alogical.
I follow Postgresql documentation for writing sql code. I want to
write comments in functions. I want to write braces in literals.

Сan one of the developers answer me or register this bug?


20 січня 2012 р. 15:14 Florent Guillaume <fg@nuxeo.com> написав:
> 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.lytovchenko@gmail.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@nuxeo.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.lytovchenko@gmail.com>:
>>>> ---------- Переслане повідомлення ----------
>>>> Від: Роман Литовченко <roman.lytovchenko@gmail.com>
>>>> Дата: 19 січня 2012 р. 11:02
>>>> Тема: Re: [JDBC] plpgsql function, comment with single quote, braces
>>>> Кому: David Johnston <polobo@yahoo.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@yahoo.com>:
>>>>> -----Original Message-----
>>>>> From: pgsql-jdbc-owner@postgresql.org
>>>>> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of ????? ??????????
>>>>> Sent: Wednesday, January 18, 2012 4:20 PM
>>>>> To: pgsql-jdbc@postgresql.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@postgresql.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