Transform query results values in PostgreSQL

Question

I have a set of numeric identifiers (e.g. 100, 101, 102, etc) that map to actions a user can take. I'd like to be able to transform those values into human readable strings so I can run a report and not have to include a legend key with it.

I took a preliminary stab at creating a function

CREATE FUNCTION action_to_str (integer) RETURNS text  as $$
    BEGIN
        IF $1 = 100 THEN
          RETURN "skip";
        ELSIF $1 = 101 THEN
            RETURN "save";
        ELSIF $1 = 102 THEN
            RETURN "buy";
        ELSIF $1 = 103 THEN
            RETURN "map";
        ELSIF $1 = 104 THEN
            RETURN "dismiss";
        ELSIF $1 = 105 THEN
            RETURN "share";
        ELSIF $1 = 106 THEN
            RETURN "dislike";
        ELSE
            RETURN "unknown";
        END IF;
    END;
$$ LANGUAGE plpgsql;

But then I get an error like this:

ERROR:  column "dismiss" does not exist
LINE 1: SELECT "dismiss"
               ^
QUERY:  SELECT "dismiss"
CONTEXT:  PL/pgSQL function action_to_str(integer) line 12 at RETURN

I understand what's going on (it's thinking that the result of the function is a column not a value) but I'm not sure how to work around this.

Clodoaldo Neto · Accepted Answer · 2014-07-16 16:50:27Z

1

The main problem is that you are double quoting (") the returned values. Use single quotes (') instead. Double quotes are used for identifiers (not recommended). Single quotes for values.

The best practice is to create a table action

create table action (
    action_id int,
    action_description text
);

and join it in the query

select coalesce(a.action_description, 'unknown')
from
    some_table st
    left join
    action a using(action_id);

The mantra to get used to is: think relational not procedural

edited Jul 16, 2014 at 16:50

answered Jul 16, 2014 at 16:37

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Transform query results values in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related