ERROR: syntax error at or near "SELECT" when creating function

Question

I tried to create a function with this syntax

CREATE OR REPLACE FUNCTION prebuy_agreement.get_agreements_by_query(query JSONB)
  RETURNS SETOF JSONB AS $$
SELECT agm.*, array_agg(sd.*) as supporting_documents
FROM prebuy_agreement.agreement_master agm
         LEFT JOIN prebuy_agreement.supporting_document sd
                   ON sd.agreement_id = agm.id

WHERE ((query->>'contract_ids') IS NULL OR agm.id IN (select prebuy_agreement_v1_api.json_array_castint((query->>'contract_ids')::JSON)))
            AND ((query->>'accom_ids') IS NULL OR agm.accom_id IN ARRAY(select * from json_array_elements_text((query->>'accom_ids')::JSON)))
            AND ((query->>'mm_name') IS NULL OR agm.mm_name ILIKE '%'||(query->>'mm_name')||'%')
            AND ((query->>'status') IS NULL OR agm.status ILIKE '%'||(query->>'status')||'%')
GROUP BY agm.id
$$ 
LANGUAGE plpgsql
  SECURITY DEFINER;

but it gave me error

ERROR:  syntax error at or near "SELECT"
LINE 3: SELECT agm.*, array_agg(sd.*) as supporting_documents
        ^
SQL state: 42601
Character: 110

i don't really understand the error message. Is it because the object i returned is not a set of JSONB? is there a way to make this works?

ERROR: syntax error at or near "SELECT" when creating function

0 Answers0