Search Postgresql Archives

Easier string concat in PL funcs?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux