PostgreSQL - generar_query_update con execute

-- Function: utils.psp_generar_query_update(character varying, character varying)

-- DROP FUNCTION utils.psp_generar_query_update(character varying, character varying);

CREATE OR REPLACE FUNCTION utils.psp_generar_query_update(p_schemaname character varying, p_tablename character varying)
RETURNS text AS
$BODY$
DECLARE
oPkDef REFCURSOR ;
oRecord record;

oquery character varying;
oFilterWhere character varying;
oTableName character varying;
-- oExisteIdioma boolean;
BEGIN





--oFilterWhere:='';
oquery:= '';--|| CHR(13) || CHR(10) || CHR(13) || CHR(10) ;


OPEN oPkDef FOR SELECT utils.psp_listar_no_claves(p_schemaname, p_tablename); -- Todos Los Campos exepto las claves
oTableName := trim(p_schemaname) || '.' || p_tablename;

FETCH oPkDef INTO oPkDef;

-- LISTA DE TABLAS
oquery:= oquery || CHR(9) || CHR(9) || ' v_query:= ' || quote_literal( 'UPDATE ' || oTableName || ' SET ') ||' ; '|| CHR(13) || CHR(10)|| CHR(13) || CHR(10);


LOOP
FETCH oPkDef INTO oRecord ;

IF NOT FOUND THEN
EXIT ;
END IF;



IF (oRecord.data_type!='USER-DEFINED') THEN
oquery:= oquery || CHR(9) || CHR(9) ||' IF (NOT p_' || oRecord.column_name || ' ISNULL) THEN ' || CHR(13) || CHR(10) || CHR(13) || CHR(10);

oquery:= oquery || CHR(9) || CHR(9) || CHR(9) || 'v_query := v_query || ' || quote_literal(oRecord.column_name || ' = ');

IF (oRecord.data_type='character varying' or oRecord.data_type='character varying' or oRecord.data_type='text') THEN
oquery:= oquery || ' || quote_literal(p_' || oRecord.column_name || ')' ;
ELSE
oquery:= oquery || ' || p_' || oRecord.column_name || '::' || 'varchar';
END IF;
oquery:= oquery || ' || ' || quote_literal(' ,') || ' ; ';
oquery:= oquery|| CHR(13) || CHR(10);
oquery:= oquery || CHR(9) || CHR(9) || ' END IF; ' || CHR(13) || CHR(10)|| CHR(13) || CHR(10);
END IF;



END LOOP ;

CLOSE oPkDef ;

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

oquery:= oquery || CHR(9) || CHR(9) || ' v_query:=TRIM(v_query); ' || CHR(13) || CHR(10)|| CHR(13) || CHR(10);
oquery:= oquery || CHR(9) || CHR(9) || ' v_query:=SUBSTRING(v_query,1,length(v_query)-1); '|| CHR(13) || CHR(10)|| CHR(13) || CHR(10);


---generar filtro

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:=quote_literal( ' WHERE ') || ' || ' ;
ELSE
oFilterWhere:= oFilterWhere || ' || ' || quote_literal( ' AND ') || ' ||';

END IF;
oFilterWhere:= oFilterWhere || quote_literal( oRecord.column_name || ' = ' ) ;

IF (oRecord.data_type='character varying' or oRecord.data_type='character varying' or oRecord.data_type='text') THEN
oFilterWhere:= oFilterWhere || ' || quote_literal(p_' || oRecord.column_name || ')' ;

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

END IF;
--oFilterWhere:= oFilterWhere || CHR(13) || CHR(10);

--RAISE NOTICE ' nombre pk %', oRecord.pk_name;
--RAISE NOTICE ' existe funcion %', oExisteFuncion ;
--RAISE NOTICE ' filtro 1 %', oFilterWhere;

END LOOP ;

CLOSE oPkDef ;



oquery := oquery || CHR(9) || CHR(9) || ' v_query:= v_query || ' || oFilterWhere || ';' || CHR(13) || CHR(10) || CHR(13) || CHR(10);
-- oquery := oquery || ' ' || oFilterWhere;

oquery := oquery || CHR(9) || CHR(9) || ' EXECUTE v_query; ';


--RAISE NOTICE ' %', oquery ;

RETURN oquery;

END;
$BODY$
LANGUAGE plpgsql VOLATILE