Sample Header Ad - 728x90

How to specify with a parameter a field to extract from a RECORD variable

2 votes
1 answer
1811 views
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
Asked by S&#233;bastien Cl&#233;ment (1825 rep)
Jan 24, 2018, 09:58 PM
Last activity: Jul 7, 2025, 07:05 PM