I am executing a migration that creates a new table and a new procedure to insert some records to that table, because it has a reference to a table that already exists and may have some records. At the end of the migration, the procedure is dropped.
I am calling the procedure using a SELECT statement (Postgresql).
This is the content of my migration:
CREATE TABLE empresa_pacs (
tf_habilitar character varying(5) NOT NULL,
tf_modo character varying,
tf_certificado character varying,
tf_vencimiento_certificado date,
tf_contrasena character varying,
empresa_id integer PRIMARY KEY REFERENCES empresa ON DELETE CASCADE
);
CREATE FUNCTION inserta_empresa_pacs() RETURNS VOID
AS 'DECLARE empresa_temp empresa%ROWTYPE;
BEGIN FOR empresa_temp IN SELECT * FROM empresa LOOP INSERT INTO empresa_pacs(empresa_id, tf_habilitar) VALUES(empresa_temp.empresa_id, ''false'');
END LOOP;
END;'
LANGUAGE plpgsql;
SELECT inserta_empresa_pacs() ;
DROP FUNCTION inserta_empresa_pacs();
I understand the error's meaning, I am asking if there are any other way to execute the migration to avoid the error.
Thanks in advance.
Miguel Torres.