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 usoRETURNS void
lugar.Uso
text
lugar decharacter 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);
Yo (ab-) uso el parámetro de entrada
_tbl
para tres propósitos aquí:- Proporciona el tipo bien definido del registro.
- Proporciona el nombre de la tabla, automáticamente calificado por el esquema
- Sirve como variable.
Más explicación en esta respuesta relacionada (último capítulo):
Refactorice una función PL / pgSQL para devolver la salida de varias consultas SELECT