CODEWITHSUNDEEP
postgresql
veera
veera

Reputation: 11

In clause in postgres

Need Output from table with in clause in PostgreSQL

I tried to make loop or ids passed from my code. I did same to update the rows dynamically, but for select I m not getting values from DB

CREATE OR REPLACE FUNCTION dashboard.rspgetpendingdispatchbyaccountgroupidandbranchid(
IN accountgroupIdCol    numeric(8,0),
IN branchidcol      character varying 
)
RETURNS void
AS
$$
DECLARE 
    ArrayText text[];
    i int;
BEGIN
     select string_to_array(branchidcol, ',') into ArrayText; 
     i := 1;
     loop  
     if i > array_upper(ArrayText, 1) then
     exit;
     else
        SELECT 
        pd.branchid,pd.totallr,pd.totalarticle,pd.totalweight,
        pd.totalamount
        FROM dashboard.pendingdispatch AS pd
        WHERE
        pd.accountgroupid = accountgroupIdCol AND pd.branchid IN(ArrayText[i]::numeric);    
        i := i + 1;
    end if;
    END LOOP;
END;
$$ LANGUAGE 'plpgsql' VOLATILE;

Upvotes: 1

Views: 483

Answers (1)

user330315
user330315

Reputation:

There is no need for a loop (or PL/pgSQL actually)

You can use the array directly in the query, e.g.: 

where pd.branchid = any (string_to_array(branchidcol, ','));

But your function does not return anything, so obviously you won't get a result.

If you want to return the result of that SELECT query, you need to define the function as returns table (...) and then use return query - or even better make it a SQL function:

CREATE OR REPLACE FUNCTION dashboard.rspgetpendingdispatchbyaccountgroupidandbranchid(
  IN accountgroupIdCol    numeric(8,0),
  IN branchidcol      character varying )
RETURNS table(branchid integer, totallr integer, totalarticle integer, totalweight numeric, totalamount integer)
AS
$$
  SELECT pd.branchid,pd.totallr,pd.totalarticle,pd.totalweight, pd.totalamount
  FROM dashboard.pendingdispatch AS pd
  WHERE pd.accountgroupid = accountgroupIdCol
    AND pd.branchid = any (string_to_array(branchidcol, ',')::numeric[]);
$$ 
LANGUAGE sql 
VOLATILE;

Note that I guessed the data types for the columns of the query based on their names. You have to adjust the line with returns table (...) to match the data types of the select columns.

Upvotes: 1

Related Questions