I am trying to create a function that returns a SETOF
custom types that take their content from a table this way:
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')
;
CREATE TYPE User_Data AS (
id INTEGER,
full_name VARCHAR(510),
age INTEGER
);
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;
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?
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.