Using PostgreSQL, column values from a table for 1st record are stored in a record variable. for ex: let the variable be: recordvar
recordvar.columnname
gives the value of the column name specified. I will define the columname
in a variable:
var := columnname
In place of columnname
if I replace with the variable i.e. recordvar.var
, it is not working.
Please let me know how to proceed in this situation. Following is the sample code:
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;
Working with this dummy table
CREATE TEMP TABLE foo (id int, my_num numeric);
INSERT INTO foo VALUES (1, 12.34)
First, I simplified and sanitized your example:
Removed some noise that is irrelevant to the question.
RETURNS SETOF void
hardly makes sense. I use RETURNS void
instead.
I use text
instead of character varying
, just for the sake of simplicity.
When using dynamic SQL, you have to safeguard against SQL injection, I use format()
with %I
in this case. There are other ways.
The basic problem is that SQL is very rigid with types and identifiers. You are operating with dynamic table name as well as with dynamic field name of a record - an anonymous record in your original example. Pl/pgSQL is not well equipped to deal with this. Postgres does not know what's inside an anonymous record. Only after you assign the record to a well known type can you reference individual fields.
Here is a closely related question, trying to set a field of a record with dynamic name:
How to set value of composite variable field using dynamic 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;
Call:
SELECT * from getrowdata1('foo', 1);
The commented part would raise an exception:
could not identify column "my_num" in record data type: SELECT * from getrowdata(1,'foo')
hstore
You need to install the additional module hstore for this. Once per database with:
CREATE EXTENSION hstore;
Then all could work like this:
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;
Call:
SELECT * from getrowdata2('foo', 1);
Alternative without installing additional modules.
Since you select a whole row into your record variable, there is a well defined type for it per definition. Use it. The key word is polymorphic types.
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;
Call:
SELECT * from getrowdata3(NULL::foo, 1);
I (ab-)use the input parameter _tbl
for three purposes here:
More explanation in this related answer (last chapter):
Refactor a PL/pgSQL function to return the output of various SELECT queries