Обсуждение: references/tutorial/tricks on dynamic generation of sql (& plpgsql functions) editing/coding

I'd write a trigger that generate a function.
This is done for performance reasons.
The function will be called several times and it is a list of delete statement according to the content of a table.
The content of the table will seldom change and it is linked to the creation of other tables (metadata of other tables)
andI want to provide a consistent interface to the downstream developers (if any in the future) and me. 

Since the code inside the function is getting a bit longer than what I was used I'm getting crazy about double quotes,
syntaxhighlight and such. 
I'm using pg 7.4 so $$ trick shouldn't work.

Do you have any advice about dynamically generated functions? Starting from a good vi/kate/whatever syntax highlighter
tocoding tricks to make the process less painful? 

Even if this is just the framework it looks enough unreadable

create or replace function SP_GarbageCollectionGenerate(
)
returns bool as '
begin
    execute ''create or replace SP_GarbageCollection( ''
    || '')''
    ||    ''returns bool as '''' -- what a mess!
    ||    begin
            return null;
        end;
    '' language plpgsql;
end;
' language plpgsql;


thx

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

On 9/11/06, Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:
> I'd write a trigger that generate a function.
> This is done for performance reasons.
> The function will be called several times and it is a list of delete statement according to the content of a table.
> The content of the table will seldom change and it is linked to the creation of other tables (metadata of other
tables)and I want to provide a consistent interface to the downstream developers (if any in the future) and me. 
>
> Since the code inside the function is getting a bit longer than what I was used I'm getting crazy about double
quotes,syntax highlight and such. 
> I'm using pg 7.4 so $$ trick shouldn't work.
>
> Do you have any advice about dynamically generated functions? Starting from a good vi/kate/whatever syntax
highlighterto coding tricks to make the process less painful? 

Although I wrote such functions for a while, I found them to be
unmaintanable.  As soon as 8.0 came out, I converted everything I had
to dollar quoting as quickly as possible.  Dollar quoting literally
transformed pl/pgsql into an amazing productive langauge.  I strongly
advise you to consider this against whatever objections you have to
upgrading postgresql to a recent version.

merlin

> I'm using pg 7.4 so $$ trick shouldn't work.
Should work. if you put enugh $$, somebody might backport this for you ;)


how about:

create or replace function SP_GarbageCollectionGenerate()
returns bool as '
declare
v_query text;
begin
    v_qyery=''create or replace blah blah
    :para1 blah blah
    blah blah :para2
    '';
    v_query=replace(v_query,'':para1'',quote_literal(value_1));
    v_query=replace(v_query,'':para2'',quote_ident(value_2));
    execute v_query;

end;
' language plpgsql;

a bit more readable, I think.

Greetings
Marcin

On Sep 11, 2006, at 8:34 AM, Ivan Sergio Borgonovo wrote:

> Since the code inside the function is getting a bit longer than
> what I was used I'm getting crazy about double quotes, syntax
> highlight and such.
> I'm using pg 7.4 so $$ trick shouldn't work.

If you can't update to 8.0 or later, you might want to look at
pgEdit. It can correctly syntax highlight pl/pgsql functions with
nested quotes.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

On Mon, 11 Sep 2006 09:36:28 -0400
John DeSoi <desoi@pgedit.com> wrote:

No matter how many $$ I put in my sql code it won't work in 7.4 ;)
And I bet (ooh not seriously, since it's not the most proficient way to invest my $$) pgsql 8.X has been backported in
sargeand it should already be in etch (that should be ready before I'll finish my pgsql project). 
Anyway I'm quite conservative in administrative related stuff... since I'm not a sysadmin and I don't want to rely on
anythingthat gives me a feeling of not being under my control. 

> On Sep 11, 2006, at 8:34 AM, Ivan Sergio Borgonovo wrote:
>
> > Since the code inside the function is getting a bit longer than
> > what I was used I'm getting crazy about double quotes, syntax
> > highlight and such.
> > I'm using pg 7.4 so $$ trick shouldn't work.

> If you can't update to 8.0 or later, you might want to look at
> pgEdit. It can correctly syntax highlight pl/pgsql functions with
> nested quotes.

I came across your website just few minutes ago looking exactly for that.
Second thing I did was feeling solidarity towards your "brave choice" of using pg with drupal.
Third thing I did was to aptitude search gedit in my sid with no luck :(
At a second look I saw your tool is not available for Linux.

syntax highlight would help.
Any other technique/suggestion to code dynamically generated function?

thx


--
Ivan Sergio Borgonovo
http://www.webthatworks.it