CODEWITHSUNDEEP
sqlpostgresql
Piotr
Piotr

Reputation: 197

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

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!

Upvotes: 0

Views: 73

Answers (1)

Patrick
Patrick

Reputation: 32234

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;

Upvotes: 1

Related Questions