Re: plpgsql function, comment with single quote, braces

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