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

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