CREATE TABLE is not allowed in a non-volatile function

Question

I have the following three tables :

create table drugs(
    id integer,
    name varchar(20),
    primary key(id)
);
create table prescription(
    id integer,
    drug_id integer,
    primary key(id),
    foreign key(drug_id) references drugs(id)
);
create table visits(
    patient_id varchar(10),
    prescription_id integer,
    primary key( patient_id , prescription_id),
    foreign key(prescription_id) references prescription(id)
);

I wrote the following function on these tables to show me a patient's drugs list(the patient id is parameter):

CREATE OR REPLACE FUNCTION public.patients_drugs(
    patientid character varying)
    RETURNS TABLE(drug_id integer, drug_name character varying) 
    LANGUAGE 'plpgsql'

    COST 100
    STABLE STRICT 
    ROWS 1000

AS $BODY$
begin
    create temporary table result_table(
        drug_id integer,
        drug_name varchar(20)
    );
    return  query select distinct drug.id , drug.name 
    from  visits join prescription
    on visits.patient_id = patientID;

end;
$BODY$;

However, it gives me this error:

CREATE TABLE is not allowed in a non-volatile function

Answer 1

You don't need to create a table in order to be able to "return a table". Just get rid of the CREATE TABLE statement.

But your query isn't correct either, as you are selecting columns from the drug table, but you never include that in the FROM clause. You can also get rid of the distinct clause if you don't use a join, but an EXISTS condition:

CREATE OR REPLACE FUNCTION public.patients_drugs(p_patientid character varying)
    RETURNS TABLE(drug_id integer, drug_name character varying) 
    LANGUAGE plpgsql
AS $BODY$
begin
  return query 
    select d.*
    from drugs d
    where exists (select *
                  from prescription p
                    join visits v on v.prescription_id = p.id
                  where d.id = p.drug_id
                   and v.patientid = p_patientid);
end;
$BODY$;

Or better, use a simple SQL function:

CREATE OR REPLACE FUNCTION public.patients_drugs(p_patientid character varying)
    RETURNS TABLE(drug_id integer, drug_name character varying) 
    LANGUAGE sql
AS 
$BODY$
  select d.*
  from drugs d
  where exists (select *
                from prescription p
                  join visits v on v.prescription_id = p.id
                where d.id = p.drug_id
                  and v.patientid = p_patientid);
$BODY$;