PostgreSQL - Generar Funciones 2

--

-- TOC entry 596 (class 1255 OID 25627)

-- Name: psp_generar_asignaciones(character varying, character varying); Type: FUNCTION; Schema: utils; Owner: postgres

--

CREATE FUNCTION psp_generar_asignaciones(p_schemaname character varying, p_tablename character varying) RETURNS character varying

    LANGUAGE plpgsql

    AS $$

DECLARE

	oPkDef REFCURSOR ;

	oRecord record;

	oFieldNameList character varying;

	--oExisteFuncion boolean;

BEGIN

	oFieldNameList:='';

	OPEN oPkDef FOR SELECT  utils.psp_listar_no_claves(p_schemaname, p_tablename);

	FETCH oPkDef INTO oPkDef;

	-- LISTA DE TABLAS

	LOOP

	    FETCH oPkDef INTO oRecord ;

		IF NOT FOUND THEN

			EXIT ;

		END IF;

		IF oFieldNameList != '' THEN

		   oFieldNameList := oFieldNameList || ',' || CHR(13) || CHR(10) || CHR(9);

		END IF ;

		oFieldNameList := oFieldNameList || CHR(9) || oRecord.column_name ||' = '||'p_'|| oRecord.column_name ;

	END LOOP ;

	CLOSE oPkDef ;

	--RAISE NOTICE ' filtro %', oFieldNameList;

	RETURN oFieldNameList;

END;

$$;

ALTER FUNCTION utils.psp_generar_asignaciones(p_schemaname character varying, p_tablename character varying) OWNER TO postgres;

--

-- TOC entry 488 (class 1255 OID 25879)

-- Name: psp_generar_campos(character varying, character varying); Type: FUNCTION; Schema: utils; Owner: postgres

--

CREATE FUNCTION psp_generar_campos(p_schemaname character varying, p_tablename character varying) RETURNS character varying

    LANGUAGE plpgsql

    AS $$

DECLARE

	oPkDef REFCURSOR ;

	oRecord record;

	oFieldList character varying;

--	oExisteIdioma boolean;

BEGIN

	oFieldList:='';

	OPEN oPkDef FOR SELECT  utils.psp_listar_campos(p_schemaname, p_tablename);

	FETCH oPkDef INTO oPkDef;

	-- LISTA DE TABLAS

	LOOP

	    FETCH oPkDef INTO oRecord ;

		IF NOT FOUND THEN

			EXIT ;

		END IF;

		IF lower(trim(oRecord.column_name)) != 'id_idioma' THEN

			IF  oFieldList!='' THEN

				oFieldList:=  oFieldList || ' , ';

			END IF;

			oFieldList := oFieldList ||  oRecord.column_name  ;

		END IF ;

	END LOOP ;

	CLOSE oPkDef ;

	-- se obliga a poner el campo idioma al final de la lista

	oFieldList := oFieldList || ', id_idioma ';

	--RAISE NOTICE ' filtro %',  oFieldList;

	RETURN oFieldList;

END;

$$;

ALTER FUNCTION utils.psp_generar_campos(p_schemaname character varying, p_tablename character varying) OWNER TO postgres;

--

-- TOC entry 291 (class 1255 OID 25880)

-- Name: psp_generar_campos_parametros(character varying, character varying); Type: FUNCTION; Schema: utils; Owner: postgres

--

CREATE FUNCTION psp_generar_campos_parametros(p_schemaname character varying, p_tablename character varying) RETURNS character varying

    LANGUAGE plpgsql

    AS $$

DECLARE

	oPkDef REFCURSOR ;

	oRecord record;

	oParameterList character varying;

--	oExisteIdioma boolean;

BEGIN

	oParameterList:='';

	OPEN oPkDef FOR SELECT  utils.psp_listar_campos(p_schemaname, p_tablename);

	FETCH oPkDef INTO oPkDef;

	-- LISTA DE TABLAS

	LOOP

	    FETCH oPkDef INTO oRecord ;

		IF NOT FOUND THEN

			EXIT ;

		END IF;

		IF lower(trim(oRecord.column_name)) != 'id_idioma' THEN

			IF  oParameterList!='' THEN

				oParameterList:=  oParameterList || ' , ';

			END IF;

			oParameterList := oParameterList || 'p_' ||  oRecord.column_name  ;

		END IF ;

	END LOOP ;

	CLOSE oPkDef ;

	-- se obliga a poner el campo idioma al final de la lista

	oParameterList := oParameterList || ', p_id_idioma ';

	--RAISE NOTICE ' filtro %',  oParameterList;

	RETURN oParameterList;

END;

$$;

ALTER FUNCTION utils.psp_generar_campos_parametros(p_schemaname character varying, p_tablename character varying) OWNER TO postgres;

--

-- TOC entry 587 (class 1255 OID 25490)

-- Name: psp_generar_filtros(character varying, character varying); Type: FUNCTION; Schema: utils; Owner: postgres

--

CREATE FUNCTION psp_generar_filtros(p_schemaname character varying, p_tablename character varying) RETURNS character varying

    LANGUAGE plpgsql

    AS $$

DECLARE

	oPkDef REFCURSOR ;

	oRecord record;

	oFilterWhere character varying;

	--oExisteFuncion boolean;

BEGIN

	oFilterWhere:='';

	OPEN oPkDef FOR SELECT  utils.psp_listar_claves(p_schemaname, p_tablename);

	FETCH oPkDef INTO oPkDef;

	-- LISTA DE TABLAS

	LOOP

	    FETCH oPkDef INTO oRecord ;

		IF NOT FOUND THEN

			EXIT ;

		END IF;

		IF oFilterWhere='' THEN

		   oFilterWhere:=' WHERE ';

		ELSE

		   oFilterWhere:= oFilterWhere || ' AND ';

		END IF;

		oFilterWhere:= oFilterWhere || oRecord.column_name || '=p_' || oRecord.column_name ;

		--RAISE NOTICE ' nombre pk %', oRecord.pk_name;

		--RAISE NOTICE ' existe funcion %', oExisteFuncion ;

		--RAISE NOTICE ' filtro 1 %', oFilterWhere;

	END LOOP ;

	CLOSE oPkDef ;

	--RAISE NOTICE ' filtro %', oFilterWhere;

	RETURN oFilterWhere;

END;

$$;

ALTER FUNCTION utils.psp_generar_filtros(p_schemaname character varying, p_tablename character varying) OWNER TO postgres;

--

-- TOC entry 293 (class 1255 OID 25888)

-- Name: psp_generar_parametros(character varying, character varying, integer); Type: FUNCTION; Schema: utils; Owner: postgres

--

CREATE FUNCTION psp_generar_parametros(p_schemaname character varying, p_tablename character varying, p_tipo_parametros integer) RETURNS character varying

    LANGUAGE plpgsql

    AS $$

DECLARE

	oPkDef REFCURSOR ;

	oRecord record;

	oParameterList character varying;

--	oExisteIdioma boolean;

BEGIN

	IF p_tipo_parametros=0 THEN

	   RETURN ' p_id_idioma integer '; -- parametro unico para mensajes en distintos idiomas

	END IF;

	oParameterList:='';

	IF p_tipo_parametros=1 THEN

	   OPEN oPkDef FOR SELECT  utils.psp_listar_claves(p_schemaname, p_tablename); -- Solo los campos Clave

	ELSE

	   OPEN oPkDef FOR SELECT  utils.psp_listar_campos(p_schemaname, p_tablename); -- Todos Los Campos

	END IF;

	FETCH oPkDef INTO oPkDef;

	-- LISTA DE TABLAS

	LOOP

	    FETCH oPkDef INTO oRecord ;

		IF NOT FOUND THEN

			EXIT ;

		END IF;

		IF lower(trim(oRecord.column_name)) != 'id_idioma' THEN

			IF  oParameterList!='' THEN

				oParameterList:=  oParameterList || ' , ';

			END IF;

			oParameterList := oParameterList || 'p_' || oRecord.column_name || ' ' || oRecord.data_type ;

		END IF ;

	END LOOP ;

	CLOSE oPkDef ;

	-- se obliga a poner el parametro idioma al final de la lista

	oParameterList := oParameterList || ', p_id_idioma integer ';

	--RAISE NOTICE ' filtro %',  oParameterList;

	RETURN oParameterList;

END;

$$;

ALTER FUNCTION utils.psp_generar_parametros(p_schemaname character varying, p_tablename character varying, p_tipo_parametros integer) OWNER TO postgres;

--

-- TOC entry 552 (class 1255 OID 25114)

-- Name: psp_listar_campos(character varying, character varying); Type: FUNCTION; Schema: utils; Owner: postgres

--

CREATE FUNCTION psp_listar_campos(p_schemaname character varying, p_tablename character varying) RETURNS refcursor

    LANGUAGE plpgsql

    AS $$

DECLARE

	oFieldNames REFCURSOR;

BEGIN

	OPEN oFieldNames FOR

	SELECT column_name,data_type

	FROM

		information_schema.columns

	WHERE

		table_schema=p_schemaName and table_name = p_tableName

		;

	return oFieldNames ;

END;

$$;

ALTER FUNCTION utils.psp_listar_campos(p_schemaname character varying, p_tablename character varying) OWNER TO postgres;

--

-- TOC entry 441 (class 1255 OID 33436)

-- Name: psp_listar_campos_prueba(character varying, character varying); Type: FUNCTION; Schema: utils; Owner: postgres

--

CREATE FUNCTION psp_listar_campos_prueba(p_schemaname character varying, p_tablename character varying) RETURNS SETOF record

    LANGUAGE plpgsql

    AS $$

DECLARE

	oFieldNames record;

BEGIN

	FOR oFieldNames IN

		SELECT column_name::character varying,data_type::character varying

		 FROM information_schema.columns

		  WHERE table_schema=p_schemaName and table_name = p_tableName

	loop

		return next oFieldNames;

	end loop;

	RETURN;

END;

$$;

ALTER FUNCTION utils.psp_listar_campos_prueba(p_schemaname character varying, p_tablename character varying) OWNER TO postgres;

--

-- TOC entry 586 (class 1255 OID 25480)

-- Name: psp_listar_claves(character varying, character varying); Type: FUNCTION; Schema: utils; Owner: postgres

--

CREATE FUNCTION psp_listar_claves(p_schemaname character varying, p_tablename character varying) RETURNS refcursor

    LANGUAGE plpgsql

    AS $$

DECLARE

	oPkNames REFCURSOR;

	v_ConstraintName character varying;

BEGIN

	v_ConstraintName = utils.psp_obtener_nombre_pk(p_schemaName,p_tableName) ;

	IF v_ConstraintName IS NULL  THEN

		RAISE EXCEPTION ' Esta tabla no tiene campos claves ';

		return null;

	END IF;

	OPEN oPkNames FOR

 	 SELECT a.column_name ,a.data_type FROM information_schema.columns a

	   WHERE	(table_schema=p_schemaname and table_name =  p_tablename )

		AND column_name IN (SELECT b.column_name as pk_name

				FROM information_schema.constraint_column_usage b

					WHERE b.table_schema = p_schemaname AND b.table_name = p_tablename

						AND constraint_name = v_ConstraintName);

	--	SELECT column_name as pk_name

          --FROM information_schema.constraint_column_usage

          --WHERE table_schema = p_schemaName AND table_name = p_tableName AND constraint_name = ;

	return oPkNames ;

END;

$$;

ALTER FUNCTION utils.psp_listar_claves(p_schemaname character varying, p_tablename character varying) OWNER TO postgres;

--

-- TOC entry 408 (class 1255 OID 26589)

-- Name: psp_listar_funciones(character varying); Type: FUNCTION; Schema: utils; Owner: postgres

--

CREATE FUNCTION psp_listar_funciones(p_schemaname character varying) RETURNS refcursor

    LANGUAGE plpgsql

    AS $$

DECLARE

	oTablaNames REFCURSOR;

BEGIN

	OPEN oTablaNames FOR

	SELECT n.nspname as "Esquema",

	  p.proname as "Funcion",

	  pg_catalog.pg_get_function_result(p.oid) as "Resultado",

	  pg_catalog.pg_get_function_arguments(p.oid) as "Parametros"

	FROM pg_catalog.pg_proc p

	     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace

	WHERE n.nspname = p_schemaname

	ORDER BY 1, 2, 4;

	return oTablaNames ;

END;

$$;

ALTER FUNCTION utils.psp_listar_funciones(p_schemaname character varying) OWNER TO postgres;

--

-- TOC entry 477 (class 1255 OID 25626)

-- Name: psp_listar_no_claves(character varying, character varying); Type: FUNCTION; Schema: utils; Owner: postgres

--

CREATE FUNCTION psp_listar_no_claves(p_schemaname character varying, p_tablename character varying) RETURNS refcursor

    LANGUAGE plpgsql

    AS $$

DECLARE

	oPkNames REFCURSOR;

	v_ConstraintName character varying;

BEGIN

	v_ConstraintName = utils.psp_obtener_nombre_pk(p_schemaName,p_tableName) ;

	IF v_ConstraintName IS NULL  THEN

		RAISE EXCEPTION ' Esta tabla no tiene campos claves ';

		return null;

	END IF;

	OPEN oPkNames FOR

 	 SELECT a.column_name ,a.data_type FROM information_schema.columns a

	   WHERE	(table_schema=p_schemaname and table_name =  p_tablename )

		AND column_name NOT IN (SELECT b.column_name as pk_name

				FROM information_schema.constraint_column_usage b

					WHERE b.table_schema = p_schemaname AND b.table_name = p_tablename

						AND constraint_name = v_ConstraintName);

	return oPkNames ;

END;

$$;

ALTER FUNCTION utils.psp_listar_no_claves(p_schemaname character varying, p_tablename character varying) OWNER TO postgres;

--

-- TOC entry 608 (class 1255 OID 25110)

-- Name: psp_listar_tablas(character varying); Type: FUNCTION; Schema: utils; Owner: postgres

--

CREATE FUNCTION psp_listar_tablas(p_schemaname character varying) RETURNS refcursor

    LANGUAGE plpgsql

    AS $$

DECLARE

	oTablaNames REFCURSOR;

BEGIN

	OPEN oTablaNames FOR

	SELECT DISTINCT table_schema as schemaName,  table_name as tableName

		FROM information_schema.columns

		WHERE table_schema = p_schemaname

		order by table_name;

	return oTablaNames ;

END;

$$;

ALTER FUNCTION utils.psp_listar_tablas(p_schemaname character varying) OWNER TO postgres;