To begin with, I am a newbie to SQL and PostgreSQL. It might be a silly beginner's mistake.
create or replace function temporary_function_for_getting(admission_number_text text,organization_id bigint,user_object_json json)
returns table(admission_number text ,status text)
LANGUAGE plpgsql
AS $function$
declare
select_user_with_id text :=
'select $1 as admin,
case
when $2 is null then ''Invalid organization ID''
when $1 is null then ''Invalid admission number''
when exists (update hsg_id_master set hsg_suffix=''something new'' where admission_number=$1 and organization_id=$2 returning hsg_suffix )
then ''success''
else ''User does not exists.''
end;';
begin
return query
execute select_user_with_id using admission_number_text,organization_id;
end;
$function$ ;
^This isn't working provides me with an error
SQL Error [42601]: ERROR: syntax error at or near "update"
Where: PL/pgSQL function temporary_function_for_getting(text,bigint,json) line 13 at RETURN QUERY .
The update query works fine by itself not sure what i am doing wrong. Would appreciate if anyone could point me to the resource and/or a better way of doing the same process .