I use the behavior in the Yii application to generate a unique uri record in the database from the header of this record. When you create a record, the text from the name field is converted to translit, separated by hyphens.
If the string is not unique, the ordinal number of the string is added to the end of the string. Those. when adding three entries with name = 'any text' we will get consistently 'luboy-tekst', 'luboy-tekst-1', 'luboy-tekst-2'.
Unfortunately, within the framework of the architecture, it will not be possible to control the filling of the uri field in the tables, because the generation of this value will have to be done at the database level. As I understand it, php-intl is used in the above behavior. Are there any postgress tools for this task?

  • Unfortunately, within the framework of the architecture, it is not possible to control the filling of the uri field in the tables, because the generation of this value will have to be done at the database level - use an optimistic lock - etki

1 answer 1

To solve your problem solely on the database side, you definitely need to use triggers. In your case, you need to create a trigger that will be executed before (or instead of) adding the record. Example of creating a trigger:

CREATE TRIGGER trUriTrigger INSTEAD OF INSERT ON uriTable FOR EACH ROW EXECUTE PROCEDURE trUriProcedure();

You need to implement your trigger according to the following principle:

  • Write transliteration using the translate method. An example of using translate(lower(field_value, 'абвгдеёзийклмнопрстуфхць', 'abvgdeezijklmnoprstufхc``'))

  • Checking the received record for uniqueness. If the check is passed - add the record as it is

  • If the check is not passed, you must either create a new sequence with the name equal to transliterated text, if the sequence was not created, or obtain a new number from the sequence already created

An example of creating a sequence: CREATE SEQUENCE transliteratedURI START 1;
An example of getting a new number from a sequence: SELECT nextval('generatedUriId');

In addition, you will have to use Dynamic SQL to dynamically set a name for the sequence. Example:

 EXEC SQL BEGIN DECLARE SECTION; const char *stmt = "CREATE SEQUENCE ? START 1;"; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE mystmt FROM :stmt; EXEC SQL EXECUTE mystmt USING transliteratedURI;

where transliterated text should be stored in transliteratedURI
Learn more about triggers in PostgreSQL .
Learn more about sequences in PostgreSQL .
Learn more about dynamic SQL in PostgreSQL .

  • one
    If you still insist on a separate numbering for each page, then you can create a separate sequence for each URI. - Geslot
  • In this case, it is easier for me to add id to the transliteration from name, which is not even necessary to write to the database, because nothing will prevent it from being generated on the fly. But I just want to get away from the numerical numbering. Duplicate uri will be very small, because the numbers will rarely occur. But I have not yet found a way to do this on the side of the database. - ilyaplot
  • @ilyaplot Create a trigger BEFORE adding a record in which you check the uniqueness, if the URI is not unique - create a sequence, if it is already created - call for it nextval - Geslot
  • Ok, this is understandable, but what about the transliteration itself? - ilyaplot
  • one
    @ilyaplot Corrected the answer to more completely answer your question - Geslot