In clause in postgres

Question

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;

Do you find something like this?: https://stackoverflow.com/a/54155256 — Cepr0, Jan 16 '19 at 18:03

score 1 · Answer 1 · 2019-01-16T15:46:24.773

1

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.

edited Jan 16 '19 at 15:46

answered Jan 16 '19 at 15:37

Hi thank u for modification but with this also i m getting this error – veera Jan 16 '19 at 15:46
ERROR: operator does not exist: numeric = text LINE 10: WHERE pd.branchid = any (string_to_array(branchidcol, ',')... ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts. – veera Jan 16 '19 at 15:46
can u help in this – veera Jan 16 '19 at 15:46
My simple intension is to get the values from tables using multiples ids as we do in sql In clause – veera Jan 16 '19 at 15:47
The function will do that. Btw:it would be more efficient to use `integer` or `bigint` for `ID` columns, rather than `numeric` – Jan 16 '19 at 15:47
Thank u Very much ! It Worked For Me ! – veera Jan 16 '19 at 15:55

In clause in postgres

1 Answers1