Can I use PostgreSQL function in join?

Question

Can I use PostgreSQL function in join? also can I use cursor function in join? actually this is what I want

Select m.* from Medication m
Left Join public.GetResidentMedications(, , , , , ,) f on f.Id= m.Id

same with the cursor functions?

**Below is my function which is a cursor function and I want to join it **

CREATE OR REPLACE FUNCTION public."GetResidentMedications" (
  ref refcursor,
  _alfid integer,
  _residentid integer,
  _types varchar,
  _limits integer,
  _offsets integer
)
RETURNS refcursor AS
$body$
BEGIN
  open ref for 
    --  select * from public."GetResidentMedications"('refcursor', 25, 331, '' , 20, 1)
    with cte AS (
       select m."Id" 
       from   public."Medication" m  
       where m."AlfId" = _AlfId
            and (m."ResidentId" = _ResidentId or coalesce (_ResidentId, 0) = 0)
            and 1 = (
            
                case when 
                    'IsVerified' = ANY ('{IsVerifiedsss, IsVerifieds}') and m."IsVerified" = true then 1 else 0 end
            )
    )
    select * from  (
       table  cte order  BY "Id"  limit  _limits offset _offsets
    ) sub
    Left join (select count("Id") from cte) c(TotalRecords) on true;
  
  return ref;                                                       
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
PARALLEL UNSAFE
COST 100;

is it possible to do it?

what is meant by this ***functionname() is a set returning function ***?. sorry I am new to postgres. can you please short example? — Ali Hussain
– Ali Hussain, Commented Nov 6, 2020 at 12:01
Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. wiki.postgresql.org/wiki/… — user330315
– user330315, Commented Nov 6, 2020 at 12:09
'IsVerified' = ANY ('{IsVerifiedsss, IsVerifieds}') makes no sense - that will always evaluate to false — user330315
– user330315, Commented Nov 6, 2020 at 12:12

user330315 · Accepted Answer · 2020-11-06 12:09:50Z

1

Make your function a set-returning function by defining it as "returns table (...)". The ref cursor is not needed.

CREATE OR REPLACE FUNCTION public."GetResidentMedications" (
  _alfid integer,
  _residentid integer,
  _types varchar,
  _limits integer,
  _offsets integer
)
RETURNS table (id integer, ... other columns ... )
as
$body$
    with cte AS (
       select m."Id" 
       from   public."Medication" m  
       where m."AlfId" = _AlfId
            and (m."ResidentId" = _ResidentId or coalesce (_ResidentId, 0) = 0)
            and 1 = (
            
                case when 
                    'IsVerified' = ANY ('{IsVerifiedsss, IsVerifieds}') and m."IsVerified" = true then 1 else 0 end
            )
    )
    select * 
    from  (
       table  cte order  BY "Id"  limit  _limits offset _offsets
    ) sub
    Left join (select count("Id") from cte) c(TotalRecords) on true;
$body$
LANGUAGE sql
stable;

then you can use it like this:

select ...
from some_table
  left join "GetResidentMedications"(1,2,'bla',10,200) as g on g.id = ...;

answered Nov 6, 2020 at 12:09

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Ali Hussain Over a year ago

ok but I use refcursor as I not want to specify many of columns as a return table

user330315 Over a year ago

You will have to specify the columns at some point. If you don't do it in the function declaration you will have to do it every time you use the function. Better do it once and don't worry about it.

Collectives™ on Stack Overflow

Can I use PostgreSQL function in join?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related