fix: specify return types in category_path

Fixes #1297

Changes proposed in this pull request:

change column order of category table to match table structure of a migrated table
explicitly specify category_path return types

How to test:

docker compose build --parallel && docker compose up
create categories and assign them to a software
verify that they are displayed on the software page and that the database container does not generate errors when viewing the page
restore a database backup of a migrated database (or by checking out v2.18.0, generating data, applying migrations to v2.19.0)
open database in terminal docker compose exec -it database psql rsd-db rsd
run this in the db terminal
```
DROP FUNCTION category_path;
```

CREATE FUNCTION category_path(category_id UUID) RETURNS TABLE ( id UUID, parent UUID, community UUID, short_name VARCHAR, name VARCHAR, properties JSONB, provenance_iri VARCHAR ) LANGUAGE SQL STABLE AS $$ WITH RECURSIVE cat_path AS ( SELECT , 1 AS r_index FROM category WHERE id = category_id UNION ALL SELECT category., cat_path.r_index+1 FROM category JOIN cat_path ON category.id = cat_path.parent ) -- 1. How can we reverse the output rows without injecting a new column (r_index)? -- 2. How a table row "type" could be used here Now we have to list all columns of category explicitly -- I want to have something like * without 'r_index' to be independent from modifications of category -- 3. Maybe this could be improved by using SEARCH keyword. SELECT id, parent, community, short_name, name, properties, provenance_iri FROM cat_path ORDER BY r_index DESC; $$;


* verify that software page with assigned categories shows categories and that no database errors occur upon viewing

PR Checklist:

*   [ ] Increase version numbers in `docker-compose.yml`
*   [x] Link to a GitHub issue
*   [ ] Update documentation
*   [ ] Tests

research-software-directory / RSD-as-a-service

fix: specify return types in category_path #1299

Quality Gate passed for 'rsd-database'