postgresql types plpgsql dynamic-sql hstore

Pasando nombres de columna dinĂ¡micamente para una variable de registro en PostgreSQL



types plpgsql (1)

Usando PostgreSQL, los valores de columna de una tabla para el primer registro se almacenan en una variable de registro. por ej .: dejar que la variable sea: recordvar

recordvar.columnname

da el valor del nombre de columna especificado. columname el nombre de columname en una variable:

var := columnname

En lugar de columnname de columnname si sustituyo la variable, es decir, recordvar.var , no funciona.

Por favor, hágame saber cómo proceder en esta situación. A continuación se muestra el código de ejemplo:

CREATE OR REPLACE FUNCTION getrowdata(id numeric, table_name character varying) RETURNS SETOF void AS $BODY$ DECLARE srowdata record; reqfield character varying; value numeric; BEGIN RAISE NOTICE ''id: %'',id; reqfield:= ''columnname''; EXECUTE ''select * from datas.''||table_name||'' WHERE id = ''||id into srowdata; RAISE NOTICE ''srowdata: %'',srowdata; RAISE NOTICE ''srowdatadata.columnname: %'',srowdata.columnname; value:= srowdata.reqfield; RAISE NOTICE ''value: %'',value; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100 ROWS 1000;


Trabajando con esta mesa ficticia.

CREATE TEMP TABLE foo (id int, my_num numeric); INSERT INTO foo VALUES (1, 12.34)

Primero, simplifiqué y desinfecté tu ejemplo:

  • Se eliminó un poco de ruido que es irrelevante para la pregunta.

  • RETURNS SETOF void apenas tiene sentido. Yo uso RETURNS void lugar.

  • Uso text lugar de character varying , solo por simplicidad.

  • Cuando se utiliza SQL dinámico, debe protegerse contra la inyección de SQL, yo uso format() con %I en este caso. Hay otras formas .

El problema básico es que SQL es muy rígido con tipos e identificadores. Está operando con el nombre de la tabla dinámica , así como con el nombre del campo dinámico de un registro , un registro anónimo en su ejemplo original. Pl / pgSQL no está bien equipado para hacer frente a esto. Postgres no sabe qué hay dentro de un registro anónimo. Solo después de asignar el registro a un tipo conocido puede hacer referencia a campos individuales.
Aquí hay una pregunta estrechamente relacionada, que trata de establecer un campo de un registro con nombre dinámico:
Cómo establecer el valor del campo variable compuesto usando SQL dinámico

Función básica

CREATE OR REPLACE FUNCTION getrowdata1(table_name text, id int) RETURNS void AS $func$ DECLARE srowdata record; reqfield text := ''my_num''; -- assigning at declaration time for convenience value numeric; BEGIN RAISE NOTICE ''id: %'', id; EXECUTE format(''SELECT * FROM %I WHERE id = $1'', table_name) USING id INTO srowdata; RAISE NOTICE ''srowdata: %'', srowdata; RAISE NOTICE ''srowdatadata.my_num: %'', srowdata.my_num; /* This does not work, even with dynamic SQL EXECUTE format(''SELECT ($1).%I'', reqfield) USING srowdata INTO value; RAISE NOTICE ''value: %'', value; */ END $func$ LANGUAGE plpgsql;

Llamada:

SELECT * from getrowdata1(''foo'', 1);

La parte comentada provocaría una excepción:

no se pudo identificar la columna "my_num" en el tipo de datos de registro: SELECT * de getrowdata (1, ''foo'')

hstore

Necesitas instalar el módulo adicional hstore para esto. Una vez por base de datos con:

CREATE EXTENSION hstore;

Entonces todos podrían trabajar así:

CREATE OR REPLACE FUNCTION getrowdata2(table_name text, id int) RETURNS void AS $func$ DECLARE hstoredata hstore; reqfield text := ''my_num''; value numeric; BEGIN RAISE NOTICE ''id: %'', id; EXECUTE format(''SELECT hstore(t) FROM %I t WHERE id = $1'', table_name) USING id INTO hstoredata; RAISE NOTICE ''hstoredata: %'', hstoredata; RAISE NOTICE ''hstoredata.my_num: %'', hstoredata -> ''my_num''; value := hstoredata -> reqfield; RAISE NOTICE ''value: %'', value; END $func$ LANGUAGE plpgsql;

Llamada:

SELECT * from getrowdata2(''foo'', 1);

Tipo polimorfo

Alternativa sin instalar módulos adicionales.

Dado que selecciona una fila completa en su variable de registro, hay un tipo bien definido para ella por definición. Utilízalo La palabra clave es tipos polimórficos .

CREATE OR REPLACE FUNCTION getrowdata3(_tbl anyelement, id int) RETURNS void AS $func$ DECLARE reqfield text := ''my_num''; value numeric; BEGIN RAISE NOTICE ''id: %'', id; EXECUTE format(''SELECT * FROM %s WHERE id = $1'', pg_typeof(_tbl)) USING id INTO _tbl; RAISE NOTICE ''_tbl: %'', _tbl; RAISE NOTICE ''_tbl.my_num: %'', _tbl.my_num; EXECUTE ''SELECT ($1).'' || reqfield -- requfield must be SQLi-safe or escape USING _tbl INTO value; RAISE NOTICE ''value: %'', value; END $func$ LANGUAGE plpgsql;

Llamada:

SELECT * from getrowdata3(NULL::foo, 1);

-> SQLfiddle