for-loopplpgsqlrowtype

How to use a row field in a for loop?


I am trying to create a function that returns a SETOF custom types that take their content from a table this way:

The table

CREATE table users(
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  birthday DATE NOT NULL
);

INSERT INTO users(first_name, last_name, birthday) VALUES
  ('Albert', 'Albertson', '2000-01-01'),
  ('Bernard', 'Bernardson', '2001-06-24'),
  ('Carl', 'Carlson', '2007-11-03')
;

The type

CREATE TYPE User_Data AS (
  id INTEGER,
  full_name VARCHAR(510),
  age INTEGER
);

The function

CREATE OR REPLACE FUNCTION get_user_data()
RETURNS SETOF User_Data AS $func$
  DECLARE
    user users%ROWTYPE;
    data User_Data;
  BEGIN
    FOR user IN SELECT * FROM users LOOP
      data.id := user.id;
      data.full_name := user.first_name || ' ' || user.last_name;
      data.age := DATEDIFF(year, user.birthday, NOW());
      RETURN NEXT data;
    END LOOP;
    RETURN;
  END;
$func$
LANGUAGE plpgsql;

The problem

ERROR:  syntax error at or near "."
LINE 8:       data.id := user.id;
                             ^

Apparenty, the function cannot access the fields in the user variable, although it is of type users%ROWTYPE that does have an id.

Note that if I comment out that line, the error reports to user.first_name on the next line, indicating it is not a problem with the field but rather the user variable.

Why isn't it working and how do I fix it?


Solution

  • user is PostgreSQL's reserved keyword - it is not good postgres's variable name. if you need it, then probably you should to use double quotes:

    data.id := "user".id;
    

    In plpgsql any expression is SQL expression, so you cannot to use SQL reserved keywords there without escaping.