While looking to implement the ODBC replace() function (replace occurences
of $2 in $1 by $3), I found that it could be expressed as:

CREATE FUNCTION replace(text, text, text) RETURNS text AS '
    select
        case when position($2 in $1) = 0 or char_length($2) = 0
            then $1
            else substring($1 from 1 for position($2 in $1) - 1)
                 || $3
                 || replace(substring($1 from position($2 in $1) + char_length($2)), $2, $3)
        end;
' LANGUAGE SQL WITH (isstrict);

Now this command doesn't actually work because it requires the replace()
function to exist already.  But it does work if one first creates a stub
replace() function and then uses CREATE OR REPLACE.

(So much about the claim that procedural languages are a security hole
because they allow infinite loops.)

I was wondering whether, as a future project, we could make this more
convenient by parsing the body of the function with the binding of the
function already in effect.

Comments?

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter


Sat, 13 Oct 2001 20:15:16 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Recursive SQL functions
Peter Eisentraut <peter_e@gmx.net> writes:
> I was wondering whether, as a future project, we could make this more
> convenient by parsing the body of the function with the binding of the
> function already in effect.

Seems like a simple rearrangement of the code.  First insert the pg_proc
entry, then CommandCounterIncrement, then do the parsing/checking of the
function body.  Given the CCI, the new entry will be visible for the
checking --- and if we error out, it rolls back just fine anyway.

			regards, tom lane

Sat, 13 Oct 2001 19:39:11 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Recursive SQL functions 