1

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
0

Just do

        RAISE NOTICE 'specified field: %', input_field_name;

It's already a variable at the top. That will work if you want the column/field name. If you want the column's value you'll need to use something that provides a dynamic accessor to a row type. For that, you need.

  • jsonb (as mentioned in the comments by Abelisto)
  • hstore

That said, This whole thing is a really bad idea. When possible, you should just create a function that processes a scalar and run it over the result set.

CREATE FUNCTION f(a text)
RETURNS text
AS $$
  SELECT 'foo' + a;
$$ LANGUAGE sql;

SELECT f(x) FROM t;

Rather than doing something that's more complex and statically types the table name burried within.

CREATE FUNCTION fThatOpensACursor(col text)
RETURNS SETOF text
AS $$
  SELECT to_jsonb(t)->>col
  FROM statically_typed_table AS t;
$$ LANGUAGE sql;

SELECT fThatOpensACursor('t.myCol');
  • 1
    Thanks @Evan Carroll, but replacing current_row.input_field_name with input_field_name alone just prints the field name, not its value that changes for each row. – Sébastien Clément Jan 24 '18 at 22:10
  • could you please be a bit more explicit about your proposed solution SELECT myFunction(t.myCol) FROM t; ? – Sébastien Clément Jan 25 '18 at 15:20
  • @SébastienClément updated. – Evan Carroll Jan 25 '18 at 16:09
  • My example was just a part, for clarity, of a larger function where I loop through each row of a table and use some of the columns' values from that row to compare to a second table and return each time a new computed dataset. Thanks for your suggestion, but it's not clear to me how it applies. – Sébastien Clément Jan 26 '18 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.