Динамическое переключение имен столбцов для переменной записи в PostgreSQL

Используя PostgreSQL, значения столбцов из таблицы для первой записи сохраняются в переменной записи. для ex: пусть переменная be: recordvar

recordvar.columnname

задает значение указанного имени столбца. Я определяю columname в переменной:

var := columnname

Вместо columnname, если заменить переменную i.e. recordvar.var, она не работает.

Пожалуйста, дайте мне знать, как действовать в этой ситуации. Ниже приведен пример кода:

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;

Ответы

Ответ 1

Работа с этой фиктивной таблицей

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

Во-первых, я упростил и обработал ваш пример:

  • Удалены некоторые помехи, которые не имеют отношения к вопросу.

  • RETURNS SETOF void вряд ли имеет смысл. Вместо этого я использую RETURNS void.

  • Я использую text вместо character varying, просто для простоты.

  • При использовании динамического SQL вы должны защитить от SQL-инъекции, я использую format() с %I в этом случае. Есть и другие способы.

Основная проблема заключается в том, что SQL очень жесткая с типами и идентификаторами. Вы используете динамическое имя таблицы, а также динамическое имя поля записи - анонимную запись в исходном примере. Pl/pgSQL недостаточно хорошо разбирается в этом. Postgres не знает, что внутри анонимной записи. Только после того, как вы присвоите запись известному типу, вы можете ссылаться на отдельные поля.
Вот близкий вопрос, пытаясь установить поле записи с динамическим именем:
Как установить значение составного поля переменных с помощью динамического SQL

Основная функция

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;

Вызов:

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

Прокомментированная часть вызовет исключение:

не удалось идентифицировать столбец "my_num" в типе данных записи: SELECT * from getrowdata (1, 'Foo')

hstore

Для этого вам необходимо установить дополнительный модуль hstore. Один раз в базе данных:

CREATE EXTENSION hstore;

Тогда все могло бы работать следующим образом:

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;

Вызов:

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

Полиморфный тип

Альтернатива без установки дополнительных модулей.

Поскольку вы выбираете целую строку в свою переменную записи, для определения по определению существует четко определенный тип. Используй это. Ключевое слово полиморфные типы.

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;

Вызов:

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

→ SQLfiddle