Блог

Привет, я хочу вызвать хранимую процедуру динамически, где имя таблицы также является динамическим, и в конце я должен передать строку идентификаторов ветвей.

когда я выполняю sp, запускается, но при выполнении с моим кодом возникает ошибка

 CREATE OR REPLACE FUNCTION dashboard.rspToGetBranchWiseTurnOverDataByRegionId(
  IN accountgropidcol numeric,
  IN BookingMonthCol numeric,
  IN BookingYearCol numeric,
  IN branchidcol character varying,
  OUT accountgroupid numeric,
  OUT sourcebranchid numeric,
  OUT destinationbranchid numeric,
  OUT waybilltypeid numeric,
  OUT totallr numeric,
  OUT totalbookingamount numeric,
  OUT totaldeliveryamount numeric,
  OUT grandtotalamount numeric,
  OUT totalquantiy numeric,
  OUT totalactualweight numeric,
  OUT totalchargedweight numeric,
  OUT bookingmonth numeric,
  OUT bookingyear numeric
 )
RETURNS SETOF record AS
$BODY$ 
DECLARE
    nameid          TEXT;   -- unique id for tables concat(Year,Month)
    tableName       TEXT;   -- table name
BEGIN 
    nameid = '_' || BookingYearCol || '_' || BookingMonthCol;
    tableName = 'dashboard.BranchWiseTurnOver' || nameid;

RETURN QUERY EXECUTE 'SELECT 
                accountgroupid, 
                sourcebranchid, 
                destinationbranchid, 
                waybilltypeid,
                totallr,
                totalbookingamount,
                totaldeliveryamount, 
                grandtotalamount,
                totalquantity,
                totalactualweight,
                totalchargedweight, 
                bookingmonth,
                bookingyear
                FROM '|| tableName || '
            where 
                accountgroupid  = ' || accountGropIdCol :: numeric || '
            AND bookingmonth    = ' || bookingMonthCol :: numeric || '
            AND bookingyear     = ' || bookingYearCol :: numeric || '
            AND SourceBranchId  = ' || any (string_to_array(branchidcol, ',')::numeric[]) || '' ;

END;
$BODY$  LANGUAGE plpgsql VOLATILE
  

Это моя хранимая процедура, в которой я получаю ошибку в любой (string_to_array(branchidcol, ‘,’)::numeric []) этой строке

журнал ошибок:

 ERROR: op ANY/ALL (array) requires operator to yield boolean
Where: PL/pgSQL function dashboard.rsptogetbranchwiseturnoverdatabyregionid(numeric,numeric,numeric,character varying) line 9 at RETURN QUERY
  

Наконец-то найдено решение

 CREATE OR REPLACE FUNCTION dashboard.rspToGetBranchWiseTurnOverDataByRegionId(
  IN accountgropidcol numeric,
  IN BookingMonthCol numeric,
  IN BookingYearCol numeric,
  IN branchidcol character varying,
  OUT accountgroupid numeric,
  OUT sourcebranchid numeric,
  OUT destinationbranchid numeric,
  OUT waybilltypeid numeric,
  OUT totallr numeric,
  OUT totalbookingamount numeric,
  OUT totaldeliveryamount numeric,
  OUT grandtotalamount numeric,
  OUT totalquantiy numeric,
  OUT totalactualweight numeric,
  OUT totalchargedweight numeric,
  OUT bookingmonth numeric,
  OUT bookingyear numeric
 )
RETURNS SETOF record AS
$BODY$ 
DECLARE
    nameid          TEXT;   -- unique id for tables concat(Year,Month)
    tableName       TEXT;   -- table name
BEGIN 
    nameid = '_' || BookingYearCol || '_' || BookingMonthCol;
    tableName = 'dashboard.BranchWiseTurnOver' || nameid;

RETURN QUERY EXECUTE 'SELECT 
                accountgroupid, 
                sourcebranchid, 
                destinationbranchid, 
                waybilltypeid,
                totallr,
                totalbookingamount,
                totaldeliveryamount, 
                grandtotalamount,
                totalquantity,
                totalactualweight,
                totalchargedweight, 
                bookingmonth,
                bookingyear
                FROM '|| tableName || '
            where 
                accountgroupid  = ' || accountGropIdCol :: numeric || '
            AND bookingmonth    = ' || bookingMonthCol :: numeric || '
            AND bookingyear     = ' || bookingYearCol :: numeric || '
            AND sourcebranchid  IN (' || branchidcol :: Character varying || ')' ;

END;
$BODY$  LANGUAGE plpgsql VOLATILE
  

Измененная хранимая процедура сработала.