Easier string concat in PL funcs?

From: Jerry Sievers <jerry(at)jerrysievers(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Easier string concat in PL funcs?
Date: 2007-10-03 15:47:26
Message-ID: m3myv019pt.fsf@mama.jerrysievers.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message hafordjr 2007-10-03 15:59:18 Windows vista
Previous Message Matt White 2007-10-03 15:39:59 SLOW Remote Connection to PostgreSQL Database