Greetings.
I have a perl script that builds a plpgsql function called get_name(t
text, id text) based on the table structure of a database. It builds
the function just fine: I can run its output in pgAdmin's query editor
and then the function works as expected.
The problem is, this is part of a larger script that updates functions
and views when the database structure changes, so I don't want to run
SQL code to replace get_name unless it has actually changed, because
it clutters up the generated update script. No problem, I thought,
I'll just run
select prosrc from pg_proc where proname = 'get_name' and proargnames
= '{t, id}'
...and compare with the generated function body. If they're the same,
I won't bother including a CREATE OR REPLACE FUNCTION statement in the
output.
The oddest thing happens, though, where running the script right after
I've used its previous output to create the function, it thinks
they're different, even though they should be identical, because I'm
the only one on here and have done nothing to the db structure in
between these steps.
So I use HTML::Diff to find out what's different, thinking it's just
maybe something like tabs or spaces getting changed around. The
weirdest thing is, it claims there are a whole bunch of things
different that are actually the same (at least visually, to me):
IF t = <del>'address' </del><ins>'address' </ins>THEN OPEN c FOR
EXECUTE <del>'SELECT ''address'' </del><ins>'SELECT ''address'' </
ins>|| a.id::text FROM address a WHERE a.id= <del>' </del><ins>' </
ins>|| id;
...and every line showing a diff follows that pattern.
I know this may seem like a perl issue, which could (also) be true,
but I'm wondering if pgsql stores its function bodies in some internal
encoding or something, versus utf8 that I use as the db encoding and I
thought perl uses internally?
That statement came from this in perl:
$sql = "IF t = '$table' THEN OPEN c FOR EXECUTE '$sql' || id;";
The thing I don't understand is how it seems to be inconsistent. All
three diffs involve a space, one each within $table and $sql, and one
outside the original $sql, right after the last apostrophe in the new
$sql. But there are lots of spaces within the original $sql and the
new $sql in this line that it does not say are different. I thought
maybe there were tabs or something hidden in the source code, but I
verified that there aren't. I even opened the source in a hex editor
and, for example, the space between EXECUTE and '$sql' and the space
after '$sql' in the line above are both 20. Why should diff think
that the former is the same and the latter not the same when we're
comparing the function as generated using that $sql line with what's
stored in the database, which came from that very line moments
earlier?
If I'm barking up the wrong tree, I'll try posting on a perl
newsgroup, but since I wasn't sure, I thought I'd try here first.
Anyone have any ideas?
Thanks,
Kev
