CODEWITHSUNDEEP
postgresqlplpgsql
S. Costa
S. Costa

Reputation: 17

Copying row from one table to another?

I have these three tables for services, executed_services and a rating table, which rates the service. My rating table has a foreign key to executed_services, and so, I wanted to "copy" the service to be rated to the executed_services relation. I tried using the following procedure, but this select won't work, as it has to return only one result.

    BEGIN
        INSERT INTO servico_executado (id, data_abertura, id_solicitacao, id_profissional) 
            VALUES (SELECT id, data_abertura, id_solicitacao, id_profissional FROM servico WHERE id = NEW.id_servico);
        DELETE FROM servico WHERE id = NEW.id_servico;
        RETURN NEW;
    END;

So, what should I do to get all the values from one table and insert on another? Or is there other way to do that?

Upvotes: 0

Views: 83

Answers (2)

redneb
redneb

Reputation: 23840

You can do it very easily in one step with a CTE with the following simple query:

WITH deleted AS (DELETE FROM servico WHERE id = $1 RETURNING *)
INSERT INTO servico_executado (id, data_abertura, id_solicitacao, id_profissional) 
SELECT id, data_abertura, id_solicitacao, id_profissional FROM deleted
RETURNING *;

There is no need to use PL/pgSQL. The id of the service moved is denoted by the placeholder $1 in the above query.

Upvotes: 1

Jay Kumar R
Jay Kumar R

Reputation: 537

 INSERT INTO servico_executado 
       SELECT id, data_abertura, id_solicitacao, id_profissional 
       FROM servico WHERE id = NEW.id_servico;

Do not use 'VALUES'. If you have more columns in the table, you can use null in the select statement for those missing columns.

Upvotes: 0

Related Questions