Adding logic to a PostgreSQL stored procedure

Question

I'm using PostgreSQL (8.3+) and have defined an enum and a table as follows:

CREATE TYPE "viewer_action" AS ENUM ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H');

CREATE TABLE "preferences"  ( 
    "user_id"       integer NOT NULL,
    "item_id"       integer NOT NULL,
    "rating"        viewer_action NOT NULL,
    "time_created"  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY ("user_id","video_id")
);

I've also created a stored procedure to upsert new rows into the preferences table, using the example from http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE:

CREATE OR REPLACE FUNCTION add_preference(u INT, i INT, r viewer_action) RETURNS VOID AS $add_preference$
    BEGIN
        LOOP
            -- first try to update the key
            UPDATE preferences SET rating = r WHERE user_id = u AND item_id = i;
            IF found THEN
                RETURN;
            END IF;
            -- not there, so try to insert the key
            -- if someone else inserts the same key concurrently,
            -- we could get a unique-key failure
            BEGIN
                INSERT INTO preferences(user_id,item_id,rating) VALUES (u,i,r);
                RETURN;
            EXCEPTION WHEN unique_violation THEN
                -- do nothing, and loop to try the UPDATE again
            END;
        END LOOP;
    END;
$add_preference$ LANGUAGE plpgsql;

I need to add some additional logic to the upsert to prevent some values from overwriting other values. Specifically:

A can be overwritten by B, which can by overwritten by C, which can be overwritten by D, and so on through F. But B cannot by overwritten by A, nor C overwritten by B, etc.
F, G, or H can overwrite any value, regardless if an existing value is lower or higher.

In pseudocode, this might look like:

if (rating >= F) {
    insert;
} else if (rating > existing_rating) {
    insert;
} else {
    return;
}

Control flow is supported in plpgsql. It seems as if you can implement your code almost directly. — nate c
– nate c, Commented Dec 20, 2010 at 22:39

Szymon Lipiński · Accepted Answer · 2011-02-15 19:27:42Z

1

Create a trigger ON BEFORE INSERT OR UPDATE on this table. For that you could use your function, with small changes like changing the return type to TRIGGER.

The BEFORE triggers are fired before inserting rows into tables, so you can check the data before writing them.

More information on triggers you can find here:
http://www.postgresql.org/docs/9.0/interactive/plpgsql-trigger.html

answered Feb 15, 2011 at 19:27

Szymon Lipiński

28.8k17 gold badges81 silver badges82 bronze badges

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

Comments

icebreaker · Accepted Answer · 2012-09-25 13:02:32Z

0

The way to look is CASE http://www.postgresql.org/docs/8.2/static/functions-conditional.html#AEN13066 it shouldn't be so difficult to implement it. And as @szymon-guz suggested, place it in trigger.

answered Sep 25, 2012 at 13:02

icebreaker

1,27418 silver badges34 bronze badges

Comments

Kuberchaun · Accepted Answer · 2012-09-25 15:53:28Z

0

Can't you do something with a case statement?

Something like this.

--If new rating is greater than existing rating update it else update to current value.
    UPDATE preferences
    SET rating = CASE WHEN r > rating THEN r ELSE rating END;

answered Sep 25, 2012 at 15:53

Kuberchaun

30.5k8 gold badges54 silver badges59 bronze badges

Collectives™ on Stack Overflow

Adding logic to a PostgreSQL stored procedure

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related