Localizing stored functions by replacing placeholders in their body

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Localizing stored functions by replacing placeholders in their body
Date: 2021-03-02 14:34:58
Message-ID: CAADeyWiVL-N0XPipT--ypL5BzCnpSe6+c8RdHaKnx6u6PoHqJA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2021-03-02 15:27:55 Re: Localizing stored functions by replacing placeholders in their body
Previous Message Reid Thompson 2021-03-02 14:07:39 Re: Pgbackrest version 2.28 Bug/Issue