[SOLVED] Unable to run a function with a select statement

Issue

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"

Solution

There are multiple mistakes

1. RETURNS instead of RETURN
2. 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)