Re: Localizing stored functions by replacing placeholders in their body
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 ...
RE: Localizing stored functions by replacing placeholders in their body
-Original Message- From: Alvaro Herrera Sent: Tuesday, March 2, 2021 2:19 PM To: Alexander Farber Cc: pgsql-general 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.
Aw: Re: Localizing stored functions by replacing placeholders in their body
> 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
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 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 Herrera39°49'30"S 73°17'W
Re: Localizing stored functions by replacing placeholders in their body
I think I will try this approach: \set localized_declaration `sed 's/this/that/' my_func.sql` :localized_declaration Thank you for your input
Re: Localizing stored functions by replacing placeholders in their body
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');
Re: Localizing stored functions by replacing placeholders in their body
út 2. 3. 2021 v 17:55 odesílatel Alexander Farber < alexander.far...@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 > 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 > > >
Re: Localizing stored functions by replacing placeholders in their body
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
Re: Localizing stored functions by replacing placeholders in their body
út 2. 3. 2021 v 17:18 odesílatel Alexander Farber < alexander.far...@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
Re: Localizing stored functions by replacing placeholders in their body
Or is it possible to call external commands from an sql script, like \i "sed 's/this/that/' some.sql"
Re: Localizing stored functions by replacing placeholders in their body
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
Re: Localizing stored functions by replacing placeholders in their body
Alexander Farber 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
Localizing stored functions by replacing placeholders in their body
Hello, I have an app using PostgreSQL 13.2, in 6 different human languages (each using different database, but same source code). Currently to localize strings return/set by the stored functions I either get localized strings from a table or maintain stored function source code in 6 different languages. This is not very comfortable and I would like to switch to using same source code (regardless of the human language) for all stored functions. And after deploying a database, just run few commands to replace placeholders in the stored functions. So I am trying: CREATE OR REPLACE FUNCTION localize_hello() RETURNS text AS $func$ SELECT '$(hello)'; $func$ LANGUAGE sql IMMUTABLE; And then: update pg_proc set prosrc = regexp_replace(prosrc, '$\(\w+\)','Hi english','g') where proname='localize_hello'; But the error is: ERROR: permission denied for table pg_proc So I connect as user "postgres" and then the command seemingly succeeds, but when I call it, the delivered string is still old: select * from localize_hello(); localize_hello $(hello) (1 row) Is this a right approach? Do you please have any advice here? Thanks Alex
