How to update data for each row using function and data from another table?

Question

I've got a problem with updating data for each row in the acrticles_pl_vector table using function in set clause and data take from another table.

For example I have a pseudocode:

CREATE OR REPLACE FUNCTION updateTsvector () 
RETURNS status AS
$$
BEGIN
    FOR EACH ROW otherTable
     UPDATE acrticles_pl_vector
     set vector = to_tsvector('polish', otherTable.title || otherTable.content);
     WHERE id = otherTable.id

END;
$$
LANGUAGE plpgsql

Thanks in advance!

Patrick · Accepted Answer · 2014-03-24 08:37:59Z

1

CREATE OR REPLACE FUNCTION updateTsvector () RETURNS void AS $$
  UPDATE acrticles_pl_vector
    SET locale = 'pl', vector = to_tsvector('polish', ot.title || ot.content
    FROM otherTable ot
    WHERE id = ot.id;
$$ LANGUAGE sql;

answered Mar 24, 2014 at 8:37

Patrick

33k7 gold badges73 silver badges102 bronze badges

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

Collectives™ on Stack Overflow

How to update data for each row using function and data from another table?

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