From: | Роман Литовченко <roman(dot)lytovchenko(at)gmail(dot)com> |
---|---|
To: | Florent Guillaume <fg(at)nuxeo(dot)com>, pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: plpgsql function, comment with single quote, braces |
Date: | 2012-01-20 13:33:29 |
Message-ID: | CALvayt=ysdq9s6N+X5Z3pSOig+shvCCak0C38upF5=eeGsLfaw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)nuxeo(dot)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(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
From | Date | Subject | |
---|---|---|---|
Next Message | Vitalii Tymchyshyn | 2012-01-20 13:54:23 | Re: NIO support |
Previous Message | Florent Guillaume | 2012-01-20 13:14:59 | Re: plpgsql function, comment with single quote, braces |