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

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

recordvar.columnname

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

var := columnname

На месте columnname если я заменю на переменную т.е. 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

Источник
Другие вопросы по тегам