Fwd: plpgsql function, comment with single quote, braces

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