references/tutorial/tricks on dynamic generation of sql (& plpgsql functions) editing/coding - Mailing list pgsql-general

From Ivan Sergio Borgonovo
Subject references/tutorial/tricks on dynamic generation of sql (& plpgsql functions) editing/coding
Date
Msg-id 20060911143400.00bb4379@localhost
Whole thread Raw
Responses Re: references/tutorial/tricks on dynamic generation of sql (& plpgsql functions) editing/coding
Re: references/tutorial/tricks on dynamic generation of sql (& plpgsql functions) editing/coding
List pgsql-general
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


pgsql-general by date:

Previous
From: Martijn van Oosterhout
Date:
Subject: Re: [ADMIN] Problem with lo_export() and lo_import() from remote machine.
Next
From: "Merlin Moncure"
Date:
Subject: Re: references/tutorial/tricks on dynamic generation of sql (& plpgsql functions) editing/coding