Insert if not exists, y si existe retorna la clave primaria

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');
Tagged with: ,
Posted in Uncategorized