I want to build a sql function that returns array of rows based on a select statement, unfortunately I couldn’t make it work.
create or replace function latestOilFee() return array IS Begin select * from oil_tracker order by ts desc fetch first 1 row only; End;
console throws an error
syntax error at or near "return"
There are multiple mistakes
- RETURNS instead of RETURN
- For ARRAY you have to mentioned like text
CREATE OR REPLACE FUNCTION latestOilFee() RETURNS text AS $$ select array[fieldname::text, fieldname::text] FROM oil_tracker limit 1 $$ LANGUAGE SQL;
Please check above, you should mentioned fields type while creating array
Answered By – Chintan Padh
Answer Checked By – Cary Denson (BugsFixing Admin)