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

types plpgsql

Question

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;

Answer 1

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

• 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