how can I sort rows by different criteria, with postgres-CodePudding

I have the following:

    id              int         GENERATED ALWAYS AS IDENTITY,
    ts              timestamp   WITHOUT TIME ZONE NOT NULL,
    urgent          bool        NOT NULL,
    origin          varchar     NOT NULL,
    message         varchar     NOT NULL,
    ts_read         timestamp   WITHOUT TIME ZONE

CREATE INDEX IF NOT EXISTS idx_alerts ON {accountId}.{tableAlertsName} (ts_read) WHERE ts_read IS NULL;

CREATE OR REPLACE VIEW {accountId}.pending_alerts AS
    SELECT id, ts, level, origin, message from {accountId}.{tableAlertsName}
    WHERE ts_read IS NULL ORDER BY urgent DESC, ts ASC;"

And I would like to replace the urgent column with a level column that would have a few options such as info, warning, error, etc.

But in my view, I would like to achieve the following sort operation:

If level = warning or level = error then add these first, if not, then sort the rest by timestamp.

How can I implement this kind of sort operation?

CodePudding user response：

You can use a union all for the view and first query will be for where clause on levels and second query will be on order by for timestamp.

CREATE OR REPLACE VIEW {accountId}.pending_alerts AS SELECT id, ts, level, origin, message from {accountId}.{tableAlertsName} WHERE (ts_read IS NULL AND (level = warning or level = error)) UNION ALL (SELECT id, ts, level, origin, message from {accountId}.{tableAlertsName} WHERE ts_read IS NULL ORDER BY urgent DESC, ts ASC)
View can be costly for performance, you can use a function to create similar scenario.

CREATE OR REPLACE FUNCTION pending_alerts () RETURNS TABLE(id int, .....) AS $$ BEGIN RETURN QUERY SELECT id, ts, level, origin, message from {accountId}.{tableAlertsName} WHERE (ts_read IS NULL AND (level = warning or level = error)) UNION ALL (SELECT id, ts, level, origin, message from {accountId}.{tableAlertsName} WHERE ts_read IS NULL ORDER BY urgent DESC, ts ASC); END; $$ LANGUAGE plpgsql;

CodePudding user response：

Are warning and error supposed to be tied with each other, or separated? If they are tied:

ORDER BY (level = 'warning' or level = 'error') DESC, ts ASC