Here's a table:
CREATE TABLE t_heights (id INT, height REAL);
INSERT INTO t_heights VALUES (1,53.63), (2,45.19), (3,47.06);
Reading through the table rows with a FOR...LOOP and a RECORD type variable like this works:
CREATE OR REPLACE FUNCTION fnct_row_by_row (input_table regclass)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
current_row RECORD;
BEGIN
FOR current_row IN EXECUTE 'SELECT * FROM '||input_table
LOOP
RAISE NOTICE 'field value: %',current_row.height; -- *<<< hardcoded field name*
END LOOP;
END;
$$
;
Results:
SELECT fnct_row_by_row ('t_heights') ;
NOTICE: field value: 53.63
NOTICE: field value: 45.19
NOTICE: field value: 47.06
However, the field extracted from the RECORD variable needs to have its name hardcoded.
How to specify dynamically the field to be extracted?
The following generates an error:
CREATE OR REPLACE FUNCTION fnct_row_by_row2 (input_table regclass, input_field_name TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
current_row RECORD;
BEGIN
FOR current_row IN EXECUTE 'SELECT * FROM '||input_table
LOOP
RAISE NOTICE 'specified field: %',current_row.input_field_name; -- *<<< field name from parameter*
END LOOP;
END;
$$
;
Execution:
SELECT fnct_row_by_row2 ('t_heights','height') ;
ERROR: record "current_row" has no field "input_field_name"
CONTEXTE : SQL statement "SELECT current_row.input_field_name"
PL/pgSQL function fnct_row_by_row2(regclass,text) line 10 at RAISE
RAISE NOTICE 'specified field: %',to_jsonb(current_row)->>input_field_name;
? – Abelisto Jan 24 '18 at 22:27