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"

CodePudding user response：

Shouldn't it be RETURNS (not RETURN)?

CodePudding user response：

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