Es un poco lió pero el truki consiste realizar dos conjuntos disjuntos: un select y un insert if not exist; luego realizar la unión de ellos dos
WITH s AS ( --Se realiza el select SELECT idminimun_studies FROM minimun_studies WHERE name = 'Licenciado - Veterinaria' ), i AS ( --Se realiza el insert if not exist INSERT into minimun_studies(name) SELECT 'Licenciado - Veterinaria' WHERE NOT EXISTS ( SELECT name FROM minimun_studies WHERE name = 'Licenciado - Veterinaria' ) RETURNING idminimun_studies ) SELECT idminimun_studies FROM i UNION ALL -- se realiza la union de los conjuntos disjuntos SELECT idminimun_studies FROM s
Es un poco lió y la solución no es óptima por no ser SQL estándar ja que la sentencia WITH (que asigna nombres a las subconsultas) no existe en Mysql aunque si en Postgres, Msql y Oracle.
Por otro lado siendo tan largo quizás es mejor crear un procedimiento en PLsql (opción que se carga la compatibilidad Ipso facto) o por el contrario realizar la lógica desde el lenguaje de cliente (opción que traslada la ilegibilidad al código de la aplicación cliente).
Implemetación de un caso general para una relación de “one to one”, notese lo intrincado del asunto
-- DROP FUNCTION insert_if_not_exist_els_return_primary_key(character varying, character varying, character varying, character varying); CREATE OR REPLACE FUNCTION insert_if_not_exist_els_return_primary_key(name_table character varying, column_id_name character varying, column_mach_name character varying, value_mach_name character varying) RETURNS integer AS $BODY$ DECLARE aaaa integer; BEGIN IF (value_mach_name IS NULL) THEN RETURN NULL; END IF; EXECUTE ' WITH s AS ( SELECT ' || quote_ident(column_id_name) || ' FROM ' || quote_ident(name_table) || ' WHERE name = ' || quote_literal(value_mach_name) || ' ), i AS ( INSERT INTO ' || quote_ident(name_table) || '(' || quote_ident(column_mach_name) || ') SELECT ' || quote_literal(value_mach_name) || ' WHERE NOT EXISTS ( SELECT ' || quote_ident(column_mach_name) || ' FROM ' || quote_ident(name_table) || ' WHERE ' || quote_ident(column_mach_name) || ' = ' || quote_literal(value_mach_name) || ' ) RETURNING ' || quote_ident(column_id_name) || ' ) SELECT ' || quote_ident(column_id_name) || ' FROM i UNION ALL SELECT ' || quote_ident(column_id_name) || ' FROM s;' INTO aaaa ; RETURN aaaa; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION insert_if_not_exist_els_return_primary_key(character varying, character varying, character varying, character varying) OWNER TO postgres; select insert_if_not_exist_els_return_primary_key('levels','idlevel','name','Registro que existe') select insert_if_not_exist_els_return_primary_key('levels','idlevel','name','Registro que NO existe') select insert_if_not_exist_els_return_primary_key('levels','idlevel','name',NULL)
Aquí otra versión de la misma función utilizando if en PL/SQL por que en alguna versiones de postgres me he encontrado que un Insert no puede ir dentro de una definición WITH
-- Function: insert_if_not_exist_els_return_primary_key(character varying, character varying, character varying, character varying) -- DROP FUNCTION insert_if_not_exist_els_return_primary_key(character varying, character varying, character varying, character varying); CREATE OR REPLACE FUNCTION insert_if_not_exist_els_return_primary_key(name_table character varying, column_id_name character varying, column_mach_name character varying, value_mach_name character varying) RETURNS integer AS $BODY$ DECLARE aaaa integer; BEGIN IF (value_mach_name IS NULL) THEN RETURN NULL; END IF; EXECUTE ' INSERT INTO ' || quote_ident(name_table) || '(' || quote_ident(column_mach_name) || ') SELECT ' || quote_literal(value_mach_name) || ' WHERE NOT EXISTS ( SELECT ' || quote_ident(column_mach_name) || ' FROM ' || quote_ident(name_table) || ' WHERE ' || quote_ident(column_mach_name) || ' = ' || quote_literal(value_mach_name) || ' ) RETURNING ' || quote_ident(column_id_name) || ' ' INTO aaaa ; IF (aaaa IS NOT NULL) THEN RETURN aaaa; ELSE EXECUTE ' SELECT ' || quote_ident(column_id_name) || ' FROM ' || quote_ident(name_table) || ' WHERE ' || quote_ident(column_mach_name) || ' = ' || quote_literal(value_mach_name) || ' ' INTO aaaa ; END IF; RETURN aaaa; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; select * from insert_if_not_exist_els_return_primary_key('countries','idcountry','name','España');