SQL - foreach column make slug form different column-CodePudding

I am new with writing complex SQL queries. I am on Postgres 14 and I I am executing migration file which has following concept.

I want to add string field with name slug which will be required like:

$this->addSql('ALTER TABLE tag ADD COLUMN slug VARCHAR NOT NULL');

As the table is already populated and my new column needs to be required

I want to: Create Null-able column, then update slug table column with valid not null values and finally ALTER column to set NOT NULL constraint!

My table (with potential slug column):

| id | name             | type | slug             |
|----|------------------|------|------------------|
|  1 | LARPs: The Series|      | larps-the-series |     
|----|------------------|------|------------------|
|  2 | #MyHaliburton.   |      | my-haliburton    |

Catch:

Added column called “slug” is is a 'slugified' version of the name (all lower case, removed punctuation, spaces replaces with dashes).

I started with:

UPDATE `tag` SET slug = lower(name),
slug = replace(slug, ':', '-'),
slug = replace(slug, '#'', ''),
...

Is this right way to cover all the cases? And yet, how to do it for all fields? Should I use FOR EACH?

Thanks

CodePudding user response：

You can do your slug generation in one command, combining a call to LOWER with two calls to REGEXP_REPLACE, one of which strips leading and trailing non-alphabetic characters, and the other which replaces non-alphabetic characters internal to the string with a - and also inserts a - when a lower-case character is followed by an upper case character:

ALTER TABLE tag ADD COLUMN slug VARCHAR;

UPDATE tag
SET slug = LOWER(
  REGEXP_REPLACE(
    REGEXP_REPLACE(name, '^[^A-Za-z] |[^A-Za-z] $', '', 'g'),
    '[^A-Za-z] |(?<=[a-z])(?=[A-Z])', '-', 'g')
  )
;

ALTER TABLE tag ALTER COLUMN slug SET NOT NULL;

Output (for your sample input):

id  name                    type    slug
1   LARPs: The Series               larps-the-series
2   #MyHaliburton.                  my-haliburton

Demo on db-fiddle