Localizing stored functions by replacing placeholders in their body

Alexander Farber <alexander.farber@gmail.com> writes:

update pg_proc set prosrc = regexp_replace(prosrc, '$\(\w+\)','Hi
english','g') where proname='localize_hello';

"$" is a metacharacter in regexes ... writing \$ might help.

(The idea of direct updates on the system catalogs seems like a really
bad one. Why not pass the code through sed or the like ahead of
feeding it to psql? But you'd have to get the regex syntax right
in any case.)

regards, tom lane

Yes, good point about the '\$', thank you Tom.

The reason I am trying not to use sed, is because I deploy my database by
executing a single command:

psql words_en < words_en.sql

And the file words_en.sql has the contents:

\i words_hash.sql
\i words_all_letters.sql
\i words_get_hint.sql
\i words_get_notification.sql
\i ../words_common.sql
\i words_valid_tile.sql
\i words_get_moves.sql
\i words_answer_puzzle.sql
\i words_rare_letter_1.sql
\i words_rare_letter_2.sql

And then the ../words_common.sql creates tables and has 40 more "\i" calls.

So I was hoping to have some SQL command to localize my stored functions.

Best regards
Alex

Or is it possible to call external commands from an sql script, like

\i "sed 's/this/that/' some.sql"

út 2. 3. 2021 v 17:18 odesílatel Alexander Farber <
alexander.farber@gmail.com> napsal:

Or is it possible to call external commands from an sql script, like

\i "sed 's/this/that/' some.sql"

you can use \! statement for execution of external statements

Pavel

Thank you for the \! hint, Pavel, didn't know about that!

Is it possible to have a pure SQL solution? (To avoid having to install
"sed" on my Win 10 PC)

Maybe by using EXECUTE?

EXECUTE REGEXP_REPLACE(
$localize$

CREATE OR REPLACE FUNCTION my_func()
RETURNS text AS
$func$
SELECT '$(placeholder)';
$func$ LANGUAGE sql IMMUTABLE;

$localize$, '\$\(\w+\)', 'English word', 'g');

Unfortunately, I get the error:

ERROR: prepared statement "regexp_replace" does not exist

út 2. 3. 2021 v 17:55 odesílatel Alexander Farber <
alexander.farber@gmail.com> napsal:

Thank you for the \! hint, Pavel, didn't know about that!

Is it possible to have a pure SQL solution? (To avoid having to install
"sed" on my Win 10 PC)

You should to use PLpgSQL EXECUTE statement, not SQL statement

https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

But I don't know - all this is server side, and you do some things on
client side

Ah, I understand, that was the wrong EXECUTE, thank you.

Another idea: can't I use \set command for my purpose of localizing stored
functions?

\set my_func_declaration `sed 's/this/that/' my_func.sql`

But how to execute the declaration? I can only echo it with

select (:'my_func_declaration');

I think I will try this approach:

\set localized_declaration `sed 's/this/that/' my_func.sql`
:localized_declaration

Thank you for your input

On 2021-Mar-02, Alexander Farber wrote:

CREATE OR REPLACE FUNCTION localize_hello()
RETURNS text AS
$func$
SELECT '$(hello)';
$func$ LANGUAGE sql IMMUTABLE;

I'm not sure this is a great approach to in-database translations: you
have one function per string, which is cumbersome, bloated and probably
slow. I would suggest having a function that takes a string and returns
its translation, which is obtained from a couple of tables: one where
the original strings are stored and another which stores the
translations for each string into each language.

(You can have the target language be a property of the database, or a
custom GUC setting that the application sets at the start, or just
passed as an argument to the translate() function from somewhere. Or
maybe each database always has exactly one language. Whatever suits you
best.)

So the functions that your application calls return strings by doing
stuff like
SELECT translate('one UFO came and stole one bike');
and they'll get whatever is right for them. The functions only need to
worry about calling translate() in all the right places; they don't need
to individually worry about fetching the translation etc.

Note that in that design, the original string appears in two places: the
function source code, and the original-strings table. You could go one
step further and have the function store a code (UUID?) for the string;
then if a message has a typo, you're just one UPDATE away from fixing it
instead of an ALTER FUNCTION. And also, it's easy to change all
translations together if your UFOs are actually ordinary burglars.

Exercise for the reader: what if your strings have format specifiers?
"%d UFOs came and stole %d bikes"

--
�lvaro Herrera 39�49'30"S 73�17'W

I'm not sure this is a great approach to in-database translations: you
have one function per string, which is cumbersome, bloated and probably
slow. I would suggest having a function that takes a string and returns
its translation, which is obtained from a couple of tables: one where
the original strings are stored and another which stores the
translations for each string into each language.

Perhaps like so

https://www.gnumed.de/documentation/schema/release/#i18n.table.curr-lang

SQL to be found here

https://github.com/ncqgm/gnumed/tree/master/gnumed/gnumed/server/sql

Karsten

-----Original Message-----
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
Sent: Tuesday, March 2, 2021 2:19 PM
To: Alexander Farber <alexander.farber@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Localizing stored functions by replacing placeholders in their body

On 2021-Mar-02, Alexander Farber wrote:

CREATE OR REPLACE FUNCTION localize_hello()
RETURNS text AS
$func$
SELECT '$(hello)';
$func$ LANGUAGE sql IMMUTABLE;

I'm not sure this is a great approach to in-database translations: you have one function per string, which is cumbersome, bloated and probably slow...

-------------------

I would agree with Alvaro and take it a step further. Perhaps you didn't do it this way, but many applications are split with back-end code to get & generate data while a set of templates is used to produce the result (HTML or whatever) the user sees. Many times these "template systems/toolkits" have I18N built into them in some way. If that's possible for you, I'd suggest investigating that. Overall, this seems more like an application problem and not a DB problem.

HTH,
Kevin
This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.

Thanks for your input

Actually, yes, that is what I have right now a translate() like stored
function, with format %s sometimes.

But that is "at runtime" and I would like to have a "at compile
time"/"deploy once and forget" solution, that is why I have asked about
approaches for modifying the bodys of my stored functions.

After some more thinking yesterday I have decided to embed the SQL files
with stored functions declarations in my servlet JAR file - that is where
the rest and most of my translations are.

I will just load them from servlet resources when the servlet start,
replace the placeholder by Java and the execute them with CREATE OR REPLACE
FUNCTION ...