Thread: references/tutorial/tricks on dynamic generation of sql (& plpgsql functions) editing/coding
references/tutorial/tricks on dynamic generation of sql (& plpgsql functions) editing/coding
From
Ivan Sergio Borgonovo
Date:
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
Re: references/tutorial/tricks on dynamic generation of sql (& plpgsql functions) editing/coding
From
"Merlin Moncure"
Date:
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
Re: references/tutorial/tricks on dynamic generation of sql (& plpgsql functions) editing/coding
From
"Marcin Mank"
Date:
> 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
Re: references/tutorial/tricks on dynamic generation of sql (& plpgsql functions) editing/coding
From
John DeSoi
Date:
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