Lists: | pgsql-general |
---|
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 |
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
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Jerry Sievers <jerry(at)jerrysievers(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Easier string concat in PL funcs? |
Date: | 2007-10-03 16:04:59 |
Message-ID: | 20071003160459.GG10624@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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)
From: | hubert depesz lubaczewski <depesz(at)depesz(dot)com> |
---|---|
To: | Jerry Sievers <jerry(at)jerrysievers(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Easier string concat in PL funcs? |
Date: | 2007-10-03 17:29:04 |
Message-ID: | 20071003172904.GA31412@depesz.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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)
From: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
---|---|
To: | depesz(at)depesz(dot)com |
Cc: | "Jerry Sievers" <jerry(at)jerrysievers(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Easier string concat in PL funcs? |
Date: | 2007-10-04 01:48:38 |
Message-ID: | b42b73150710031848k5a14584bm2f2b6ceab1a73d9d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 10/3/07, hubert depesz lubaczewski <depesz(at)depesz(dot)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