Cómo establecer el valor del campo variable compuesto usando SQL dynamic

Dado este tipo:

-- Just for testing purposes: CREATE TYPE testType as (name text) 

Puedo obtener el valor de un campo de forma dinámica con esta función:

 CREATE OR REPLACE FUNCTION get_field(object anyelement, field text) RETURNS text as $BODY$ DECLARE value text; BEGIN EXECUTE 'SELECT $1."' || field || '"' USING object INTO value; return value; END; $BODY$ LANGUAGE plpgsql 

Llamar a get_field('(david)'::testType, 'name') funciona como se esperaba al devolver “david”.

Pero, ¿cómo puedo establecer un valor de un campo en un tipo compuesto? He intentado estas funciones:

 CREATE OR REPLACE FUNCTION set_field_try1(object anyelement, field text, value text) RETURNS anyelement as $BODY$ DECLARE value text; BEGIN EXECUTE '$1."' || field || '" := $2' USING object, value; return object; END; $BODY$ LANGUAGE plpgsql CREATE OR REPLACE FUNCTION set_field_try2(object anyelement, field text, value text) RETURNS anyelement as $BODY$ DECLARE value text; BEGIN EXECUTE 'SELECT $1 INTO $2."' || field || '"' USING value, object; return object; END; $BODY$ LANGUAGE plpgsql CREATE OR REPLACE FUNCTION set_field_try3(object anyelement, field text, value text) RETURNS anyelement as $BODY$ DECLARE value text; BEGIN EXECUTE 'BEGIN $1."' || field || '" := $2; SELECT $1; END;' INTO object USING value, object; return object; END; $BODY$ LANGUAGE plpgsql 

y algunas variaciones Llamar a set_field_tryX no funciona. Siempre aparece “ERROR: error de syntax en o cerca de …”. ¿Cómo puedo lograr esto?

Notas:

  • El parámetro es un anyelement y el campo puede ser cualquier campo en el tipo compuesto. No puedo usar object.name.
  • Me preocupa la inyección SQL. Cualquier consejo en esto sería apreciado, pero no es mi pregunta.

Más rápido con hstore

Desde Postgres 9.0 , con el módulo adicional hstore instalado en su base de datos, hay una solución muy simple y rápida con el operador #= que …

reemplace [s] campos en record con valores coincidentes de hstore .

Para instalar el módulo:

 CREATE EXTENSION hstore; 

Ejemplos:

 SELECT my_record #= '"field"=>"value"'::hstore; -- with string literal SELECT my_record #= hstore(field, value); -- with values 

Los valores tienen que ser enviados al text y viceversa, obviamente.

Ejemplo de funciones de plpgsql con más detalles:

  • Bucle sin fin en la función de disparo
  • Asignar a NUEVO con la tecla en un disparador de Postgres

Casi tan rápido con json

Hay soluciones similares, pero actualmente no documentadas (a partir de la página 9.5) con json (pg 9.3+) o jsonb (pg 9.4+), integradas en Postgres, por lo que no necesita un módulo adicional.

Ver la respuesta agregada de @ Geir para más detalles.

Sin hstore y json

Si está en una versión anterior o no puede instalar el módulo adicional hstore o no puede asumir que está instalado, aquí hay una versión mejorada de lo que publiqué anteriormente. Aún más lento que el operador hstore , sin embargo:

 CREATE OR REPLACE FUNCTION f_setfield(INOUT _comp_val anyelement , _field text, _val text) RETURNS anyelement AS $func$ BEGIN EXECUTE 'SELECT ' || array_to_string(ARRAY( SELECT CASE WHEN attname = _field THEN '$2' ELSE '($1).' || quote_ident(attname) END AS fld FROM pg_catalog.pg_attribute WHERE attrelid = pg_typeof(_comp_val)::text::regclass AND attnum > 0 AND attisdropped = FALSE ORDER BY attnum ), ',') USING _comp_val, _val INTO _comp_val; END $func$ LANGUAGE plpgsql STABLE; 

Llamada:

 CREATE TEMP TABLE t( a int, b text); -- Composite type for testing SELECT f_setfield(NULL::t, 'a', '1'); 

Notas

  • No es necesario un lanzamiento explícito del valor _val al tipo de datos de destino; un literal de cadena en la consulta dinámica se forzará automáticamente, obviando la subconsulta en pg_type . Pero di un paso más:

  • Reemplazar quote_literal(_val) con inserción de valor directo a través de la cláusula USING . Guarda una llamada de función y dos lanzamientos, y de todos modos es más seguro. text se coacciona automáticamente al tipo de destino en PostgreSQL moderno. (No ha probado con versiones anteriores a la 9.1)

  • array_to_string(ARRAY()) es más rápido que string_agg() .

  • No se necesitan variables, no DECLARE . Menos asignaciones.

  • Sin subconsulta en el SQL dynamic. ($1).field es más rápido.

  • pg_typeof(_comp_val)::text::regclass
    hace lo mismo que
    (SELECT typrelid FROM pg_catalog.pg_type WHERE oid = pg_typeof($1)::oid)
    para tipos compuestos válidos, simplemente más rápido.
    Esta última modificación se basa en la suposición de que pg_type.typname es siempre idéntico al pg_class.relname asociado para los tipos compuestos registrados, y el doble molde puede reemplazar la subconsulta. Ejecuté esta prueba en una gran base de datos para verificar y salió vacía como se esperaba:

     SELECT * FROM pg_catalog.pg_type t JOIN pg_namespace n ON n.oid = t.typnamespace WHERE t.typrelid > 0 -- exclude non-composite types AND t.typrelid IS DISTINCT FROM (quote_ident(n.nspname ) || '.' || quote_ident(typname))::regclass 
  • El uso de un parámetro INOUT obvia la necesidad de un RETURN explícito. Esto es solo un atajo de notación. A Pavel no le va a gustar, prefiere una statement RETURN explícita …

Todo junto esto es casi el doble de rápido que la versión anterior.


Respuesta original (obsoleta):

El resultado es una versión que es ~ 2.25 veces más rápida . Pero probablemente no podría haberlo hecho sin basarme en la segunda versión de Pavel.

Además, esta versión evita la mayor parte de la conversión a texto y viceversa haciendo todo dentro de una sola consulta, por lo que debería ser mucho menos propensa a errores.
Probado con PostgreSQL 9.0 y 9.1 .

 CREATE FUNCTION f_setfield(_comp_val anyelement, _field text, _val text) RETURNS anyelement AS $func$ DECLARE _list text; BEGIN _list := ( SELECT string_agg(x.fld, ',') FROM ( SELECT CASE WHEN a.attname = $2 THEN quote_literal($3) || '::'|| (SELECT quote_ident(typname) FROM pg_catalog.pg_type WHERE oid = a.atttypid) ELSE quote_ident(a.attname) END AS fld FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT typrelid FROM pg_catalog.pg_type WHERE oid = pg_typeof($1)::oid) AND a.attnum > 0 AND a.attisdropped = false ORDER BY a.attnum ) x ); EXECUTE 'SELECT ' || _list || ' FROM (SELECT $1.*) x' USING $1 INTO $1; RETURN $1; END $func$ LANGUAGE plpgsql STABLE; 

Escribí una segunda versión de la función setfield. Funciona en postgres 9.1 No lo probé en versiones anteriores. No es un milagro (desde la vista de rendimiento), pero es más robusto y aproximadamente 8 veces más rápido que el anterior.

 CREATE OR REPLACE FUNCTION public.setfield2(anyelement, text, text) RETURNS anyelement LANGUAGE plpgsql AS $function$ DECLARE _name text; _values text[]; _value text; _attnum int; BEGIN FOR _name, _attnum IN SELECT a.attname, a.attnum FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT typrelid FROM pg_type WHERE oid = pg_typeof($1)::oid) AND a.attnum > 0 LOOP IF _name = $2 THEN _value := $3; ELSE EXECUTE 'SELECT (($1).' || quote_ident(_name) || ')::text' INTO _value USING $1; END IF; _values[_attnum] := COALESCE('"' || replace(replace(_value, '"', '""'), '''', '''''') || '"', ''); END LOOP; EXECUTE 'SELECT (' || quote_ident(pg_typeof($1)::text) || ' ''(' || array_to_string(_values,',') || ')'').*' INTO $1; RETURN $1; END; $function$; 

ACTUALIZACIÓN / precaución: Erwin señala que esto no está documentado actualmente , y el manual indica que no debería ser posible alterar los registros de esta manera.

Use hstore o la solución de Pavel en su lugar.

Esta solución simple basada en json es casi tan rápida como hstore, y solo requiere Postgres 9.3 o posterior. Esta debería ser una buena opción si no puede usar la extensión hstore, y la diferencia de rendimiento debería ser insignificante. Puntos de referencia: https://stackoverflow.com/a/28673542/1914376

a) Podemos hacerlo en línea mediante cast / concat. La función Json requiere Postgres 9.3:

 SELECT json_populate_record( record , ('{"'||'key'||'":"'||'new-value'||'"}')::json ); 

b) o en línea usando funciones de Postgres 9.4 .

 SELECT json_populate_record ( record ,json_object(ARRAY['key', 'new-value']) ); 

Nota: Elegí json_object (ARRAY [clave, valor]) ya que era un poco más rápido que json_build_object (clave, valor):

Para ocultar los detalles de lanzamiento, puede usar a) en una función, con poca sobrecarga.

 CREATE FUNCTION x.setfield_json(in_element anyelement, key text, value text) RETURNS anyelement AS $BODY$ SELECT json_populate_record( in_element, ('{"'||key||'":"'||value||'"}')::json); $BODY$ LANGUAGE sql; 

“SELECT INTO” fuera de plpgsql (en contexto de SQL dynamic) tiene un sentido diferente al esperado: almacena un resultado de consulta en tabla.

La modificación de cualquier campo es posible, pero no es simple

 CREATE OR REPLACE FUNCTION public.setfield(a anyelement, text, text) RETURNS anyelement LANGUAGE plpgsql AS $function$ begin create temp table aux as select $1.*; execute 'update aux set ' || quote_ident($2) || ' = ' || quote_literal($3); select into $1 * from aux; drop table aux; return $1; end; $function$ 

Pero este código no es muy efectivo, no es posible escribirlo bien en plpgsql. Puede encontrar alguna biblioteca C, eso debería hacer.

Configuración de prueba y puntos de referencia v2

Erwin animó a reproducir su punto de referencia en este hilo ( https://stackoverflow.com/a/7782839/1914376 ), así que modifiqué su código con datos sintéticos de prueba y agregué tanto la solución hstore como la solución json de mi respuesta (y una solución json de Pavel encontrada en otro hilo) El índice de referencia ahora se ejecuta como una consulta, lo que facilita la captura de los resultados.

 DROP SCHEMA IF EXISTS x CASCADE; CREATE SCHEMA x; -- Pavel 1: -------------------------------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION x.setfield(anyelement, text, text) RETURNS anyelement LANGUAGE plpgsql AS $function$ begin create temp table aux as select $1.*; execute 'update aux set ' || quote_ident($2) || ' = ' || quote_literal($3); select into $1 * from aux; drop table aux; return $1; end; $function$; -- Pavel 2 (with patches) -------------------------------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION x.setfield2(anyelement, text, text) RETURNS anyelement LANGUAGE plpgsql AS $function$ DECLARE _name text; _values text[]; _value text; _attnum int; BEGIN FOR _name, _attnum IN SELECT a.attname, a.attnum FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT typrelid FROM pg_type WHERE oid = pg_typeof($1)::oid) LOOP IF _name = $2 THEN _value := $3; ELSE EXECUTE 'SELECT (($1).' || quote_ident(_name) || ')::text' INTO _value USING $1; END IF; _values[_attnum] := COALESCE('"' || replace(replace(_value, '"', '""'), '''', '''''') || '"', ''); END LOOP; EXECUTE 'SELECT (' || pg_typeof($1)::text || '''(' || array_to_string(_values,',') || ')'').*' INTO $1; RETURN $1; END; $function$; -- Erwin 1 -------------------------------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION x.setfield3(anyelement, text, text) RETURNS anyelement AS $body$ DECLARE _list text; BEGIN _list := ( SELECT string_agg(x.fld, ',') FROM ( SELECT CASE WHEN a.attname = $2 THEN quote_literal($3) ELSE quote_ident(a.attname) END AS fld FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT typrelid FROM pg_type WHERE oid = pg_typeof($1)::oid) ORDER BY a.attnum ) x ); EXECUTE ' SELECT ' || _list || ' FROM (SELECT $1.*) x' USING $1 INTO $1; RETURN $1; END; $body$ LANGUAGE plpgsql; -- Erwin 2 -------------------------------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION x.setfield4(INOUT _comp_val anyelement , _field text, _val text) RETURNS anyelement AS $func$ BEGIN EXECUTE 'SELECT ' || array_to_string(ARRAY( SELECT CASE WHEN attname = _field THEN '$2' ELSE '($1).' || quote_ident(attname) END AS fld FROM pg_catalog.pg_attribute WHERE attrelid = pg_typeof(_comp_val)::text::regclass AND attnum > 0 AND attisdropped = FALSE ORDER BY attnum ), ',') USING _comp_val, _val INTO _comp_val; END $func$ LANGUAGE plpgsql; -- Pavel 3: json. (Postgres 9.4) -- Found here: https://stackoverflow.com/a/28284491/1914376 -------------------------------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION x.setfield5(r anyelement, fn text, val text,OUT result anyelement) RETURNS anyelement LANGUAGE plpgsql AS $function$ declare jo json; begin jo := (select json_object(array_agg(key), array_agg(case key when fn then val else value end)) from json_each_text(row_to_json(r))); result := json_populate_record(r, jo); end; $function$; -- Json. Use built-in json functions (Postgres 9.3) -- This is available from 9.3 since we create json by casting -- instead of using json_object/json_build_object only available from 9.4 -------------------------------------------------------------------------------------------------- CREATE FUNCTION x.setfield_json(in_element anyelement, key text, value text) RETURNS anyelement AS $BODY$ SELECT json_populate_record( in_element, ('{"'||key||'":"'||value||'"}')::json); $BODY$ LANGUAGE sql; -------------------------------------------------------------------------------------------------- -- Test setup -------------------------------------------------------------------------------------------------- -- composite type for tests. CREATE TYPE x.t_f as ( id int ,company text ,sort text ,log_up timestamp ,log_upby smallint ); -- Create temp table with synthetic test data DROP TABLE IF EXISTS tmp_f; CREATE TEMP table tmp_f AS SELECT ROW(i, 'company'||i, NULL, NULL, NULL)::x.t_f AS f FROM generate_series(1, 5000) S(i); -- Run the benchmark DO $$ DECLARE start_time timestamptz; test_count integer; test_description TEXT; BEGIN test_count := 200; test_description := 'setfield, Pavel 1: temptable'; start_time := clock_timestamp(); PERFORM x.setfield (f, 'company','new-value-'||md5(random()::text)) FROM tmp_f LIMIT test_count; RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description; test_count := 5000; test_description := 'setfield2, Pavel 2: reflection'; start_time := clock_timestamp(); PERFORM x.setfield2 (f, 'company','new-value-'||md5(random()::text)) FROM tmp_f LIMIT test_count; RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description; test_count := 5000; test_description := 'setfield3, Erwin 1: reflection'; start_time := clock_timestamp(); PERFORM x.setfield3 (f, 'company','new-value-'||md5(random()::text)) FROM tmp_f LIMIT test_count; RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description; test_count := 5000; test_description := 'setfield4, Erwin 2: reflection'; start_time := clock_timestamp(); PERFORM x.setfield4 (f, 'company','new-value-'||md5(random()::text)) FROM tmp_f LIMIT test_count; RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description; test_count := 5000; test_description := 'setfield5, Pavel 3: json (PG 9.4)'; start_time := clock_timestamp(); PERFORM x.setfield5 (f, 'company','new-value-'||md5(random()::text)) FROM tmp_f LIMIT test_count; RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description; test_count := 5000; test_description := 'setfield_json, Geir 1: casting (PG 9.3)'; start_time := clock_timestamp(); PERFORM x.setfield_json (f, 'company','new-value-'||md5(random()::text)) FROM tmp_f LIMIT test_count; RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description; --json_object(ARRAY(key,value]) is actually faster than json_build_object(key, value) test_count := 5000; test_description := 'no function/inlined: json_object (PG 9.4)'; start_time := clock_timestamp(); PERFORM json_populate_record( f, json_object(ARRAY['company', 'new-value'||md5(random()::text)] )) FROM tmp_f LIMIT test_count; RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description; test_count := 5000; test_description := 'no function/inlined: hstore (PG 9.0)'; start_time := clock_timestamp(); PERFORM f #= hstore('company', 'new-value'||md5(random()::text)) FROM tmp_f LIMIT test_count; RAISE NOTICE 'Test took: % ms (for % rows) Name: %', extract(MILLISECONDS FROM (clock_timestamp() - start_time))::INTEGER, test_count, test_description; END; $$; 

Resultados de la prueba en 9.4.1, win32, i5-4300U

 NOTICE: Test took: 1138 ms (for 200 rows) Name: setfield, Pavel 1: temptable NOTICE: Test took: 652 ms (for 5000 rows) Name: setfield2, Pavel 2: reflection NOTICE: Test took: 364 ms (for 5000 rows) Name: setfield3, Erwin 1: reflection NOTICE: Test took: 275 ms (for 5000 rows) Name: setfield4, Erwin 2: reflection NOTICE: Test took: 192 ms (for 5000 rows) Name: setfield5, Pavel 3: json (PG 9.4) NOTICE: Test took: 23 ms (for 5000 rows) Name: setfield_json, Geir 1: casting (PG 9.3) NOTICE: Test took: 25 ms (for 5000 rows) Name: no function/inlined: json_object (PG 9.4) NOTICE: Test took: 14 ms (for 5000 rows) Name: no function/inlined: hstore (PG 9.0) 

Actualización de marzo de 2015:
En gran parte desactualizado ahora. Considere la nueva referencia de @Geir con variantes más rápidas.


Configuración de prueba y puntos de referencia

Tomé las tres soluciones presentadas (hasta el 16 de octubre de 2011) y realicé una prueba en PostgreSQL 9.0. Encontrará la configuración completa a continuación. Solo los datos de prueba no están incluidos ya que utilicé una base de datos de la vida real (no datos sintéticos). Todo está encapsulado en su propio esquema para uso no intrusivo.

Me gustaría alentar a cualquiera que quiera reproducir la prueba. Tal vez con postgres 9.1? Y agrega tus resultados aquí? 🙂

 -- DROP SCHEMA x CASCADE; CREATE SCHEMA x; -- Pavel 1 CREATE OR REPLACE FUNCTION x.setfield(anyelement, text, text) RETURNS anyelement LANGUAGE plpgsql AS $function$ begin create temp table aux as select $1.*; execute 'update aux set ' || quote_ident($2) || ' = ' || quote_literal($3); select into $1 * from aux; drop table aux; return $1; end; $function$; -- Pavel 2 (with patches) CREATE OR REPLACE FUNCTION x.setfield2(anyelement, text, text) RETURNS anyelement LANGUAGE plpgsql AS $function$ DECLARE _name text; _values text[]; _value text; _attnum int; BEGIN FOR _name, _attnum IN SELECT a.attname, a.attnum FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT typrelid FROM pg_type WHERE oid = pg_typeof($1)::oid) LOOP IF _name = $2 THEN _value := $3; ELSE EXECUTE 'SELECT (($1).' || quote_ident(_name) || ')::text' INTO _value USING $1; END IF; _values[_attnum] := COALESCE('"' || replace(replace(_value, '"', '""'), '''', '''''') || '"', ''); END LOOP; EXECUTE 'SELECT (' || pg_typeof($1)::text || '''(' || array_to_string(_values,',') || ')'').*' INTO $1; RETURN $1; END; $function$; -- Erwin 1 CREATE OR REPLACE FUNCTION x.setfield3(anyelement, text, text) RETURNS anyelement AS $body$ DECLARE _list text; BEGIN _list := ( SELECT string_agg(x.fld, ',') FROM ( SELECT CASE WHEN a.attname = $2 THEN quote_literal($3) ELSE quote_ident(a.attname) END AS fld FROM pg_catalog.pg_attribute a WHERE a.attrelid = (SELECT typrelid FROM pg_type WHERE oid = pg_typeof($1)::oid) ORDER BY a.attnum ) x ); EXECUTE ' SELECT ' || _list || ' FROM (SELECT $1.*) x' USING $1 INTO $1; RETURN $1; END; $body$ LANGUAGE plpgsql; -- composite type for tests. CREATE TYPE x.t_f as ( id int ,company text ,sort text ,log_up timestamp ,log_upby smallint ); -- temp table with real life test data DROP TABLE IF EXISTS tmp_f; CREATE TEMP table tmp_f AS SELECT ROW(firma_id,firma,sort,log_up,log_upby)::x.t_f AS f FROM ef.firma WHERE firma !~~ '"%'; -- SELECT count(*) FROM tmp_f; -- 5183 -- Quick test: results are identical? SELECT *, x.setfield (f, 'company','test') ,x.setfield2(f, 'company','test') ,x.setfield3(f, 'company','test') FROM tmp_f LIMIT 10; 

Puntos de referencia

Ejecuté las consultas un par de veces para completar el caché. Los resultados presentados son los mejores de cinco tiempos de ejecución totales con EXPLAIN ANALYZE .

Primera ronda con 1000 filas

El primer prototipo de Pavel maximiza la memoria compartida con más filas.

Pavel 1: 2445.112 ms

 SELECT x.setfield (f, 'company','test') FROM tmp_f limit 1000; 

Pavel 2: 263.753 ms

 SELECT x.setfield2(f, 'company','test') FROM tmp_f limit 1000; 

Erwin 1: 120.671 ms

 SELECT x.setfield3(f, 'company','test') FROM tmp_f limit 1000; 

Otra prueba con 5183 filas.

Pavel 2: 1327.429 ms

 SELECT x.setfield2(f, 'company','test') FROM tmp_f; 

Erwin1: 588.691 ms

 SELECT x.setfield3(f, 'company','test') FROM tmp_f; 
    Intereting Posts