Обсуждение: 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.

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

---------- Переслане повідомлення ----------
Від: Роман Литовченко <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.
>
>

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

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

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

> 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

On Jan 20, 2012, at 8:33, Роман Литовченко <roman.lytovchenko@gmail.com> wrote:

>> 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?
>

Speaking without full knowledge here but...

This has nothing to do with PostgreSQL; in fact the database never even sees the original form of the query.  In order
tofacilitate portability ODBC and JDBC have internal escape sequences for certain things.  In this case you want to
bypassthat escaping and supply the raw SQL to the server and thus you need to follow the suggestion and turn off escape
processing. If you do this and the problem does not go away then there is a bug otherwise things are working logically
andas intended. 

If you still do not like the solution take it up with the JDBC specification writers; the people on this mailing list
arejust following instructions. 

David J.

On Fri, 20 Jan 2012, ????? ?????????? wrote:

> [simple example code]
>
> What about these?
>

Yes, this is a bug.  The escape processor is getting confused about what
it should process and is incorrectly modifying your code.  Escape
processing should only occur in a raw SQL, never within a literal value.
The escape processor should skip over the entire function body because it
is within dollar quotes and is a literal, but it doesn't understand dollar
quotes so it is examining the contents and is getting further confused by
the single quotes within it.

The parameter locating code (looking for ?) knows about dollar quotes, so
it would be nice to unify with that instead of having two partial query
parsers.

Kris Jurka