Обсуждение: Easier string concat in PL funcs?
After for the umpteenth time bashing my head against a wall developing some PL funcs that use dynamic SQL, going plain bonkers trying to build the query string; I came up with a function like the one below to take a string with placeholders, an array of values to be interpolated and a placeholder char. (This may appear Pythonish to some. Question: Am I overlooking a simple way of doing this? As in; raise notice 'Foo % %', v_var1, v_var2; create function make_string(v_template text, v_vars text[], v_placeholder char) returns text as $$ declare v_temp text[] := string_to_array(v_template, v_placeholder); v_output text[]; begin if array_upper(v_vars, 1) + 1 != array_upper(v_temp, 1) then raise exception 'Too many vars; should be equal to placeholders "%" in string', v_placeholder; end if; for i in 2 .. array_upper(v_temp, 1) * 2 by 2 loop v_output [i - 1] := v_temp[i / 2]; v_output [i] := v_vars[i / 2]; end loop; return array_to_string(v_output, ''); end $$ language plpgsql; The above function makes possible to do something like this shown below wich for complex dynamic SQL strings, can be a lot easier to create than with the usual combo of string constants pasted together with PL vars using ||. execute make_string($$ create table fooschema.% ; create rule % as on insert to fooschema.% where % do whatever ; $$, array [ v_tablename, v_rulename, v_tablename, v_conditions ], '%' ); -- ...Still not exactly simple, I realize :-) Thanks ------------------------------------------------------------------------------- Jerry Sievers 732 365-2844 (work) Production Database Administrator 305 321-1144 (mobil WWW E-Commerce Consultant
Jerry Sievers wrote: > After for the umpteenth time bashing my head against a wall developing > some PL funcs that use dynamic SQL, going plain bonkers trying to > build the query string; I came up with a function like the one below > to take a string with placeholders, an array of values to be > interpolated and a placeholder char. (This may appear Pythonish to > some. > > Question: Am I overlooking a simple way of doing this? > > As in; raise notice 'Foo % %', v_var1, v_var2; No, you aren't. AFAICT there isn't any way to do that, and I missed it not too long ago. I'm not sure about the exact syntax, and certainly I expect this to become less of an issue with plan invalidation on 8.3, but IMHO it would be good to have something like Python %(f)s string interpolation (or just plain string interpolation like in other languages). -- Alvaro Herrera http://www.advogato.org/person/alvherre "Cuando no hay humildad las personas se degradan" (A. Christie)
On Wed, Oct 03, 2007 at 11:47:26AM -0400, Jerry Sievers wrote: > Question: Am I overlooking a simple way of doing this? yes. use plpython or plperl to do the job. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV)
On 10/3/07, hubert depesz lubaczewski <depesz@depesz.com> wrote: > On Wed, Oct 03, 2007 at 11:47:26AM -0400, Jerry Sievers wrote: > > Question: Am I overlooking a simple way of doing this? > > yes. use plpython or plperl to do the job. > > depesz > here is a great example with pl/perl (search: printf) http://people.planetpostgresql.org/greg/index.php?/categories/12-PlPerl -- Parse a pipe-delimeted string: SELECT sprintf('Total grams: %3.3f Donuts: %s', '101.319472|chocolate and boston cream', '|'); sprintf --------------------------------------------------------- Total grams: 101.319 Donuts: chocolate and boston cream merlin