plpgsql - 如何在postgres函数中打印变量
问题描述
如何打印bwtid
或使用 for 执行查询
create or replace function dashboard.rspCreateAndInsertUpdateBranchWiseTurnOverTable(
AccountGroupIdCol NUMERIC(10,0),
SourceBranchIdCol NUMERIC(10,0),
DestinationBranchIdCol NUMERIC(10,0),
WayBillTypeIdCol NUMERIC(1,0),
TotalLRCol NUMERIC(20,0),
TotalBookingAmountCol NUMERIC(20,2),
TotalDeliveryAmountCol NUMERIC(20,2),
GrandTotalAmountCol NUMERIC(20,2),
TotalQuantityCol NUMERIC(20,0),
TotalActualWeightCol NUMERIC(20,3),
TotalChargedWeightCol NUMERIC(20,3),
BookingMonthCol NUMERIC(2,0),
BookingYearCol NUMERIC(4,0),
schemaName TEXT,
TableNameCol TEXT
) returns numeric as
$$
DECLARE
nameid TEXT; -- unique id for tables concat(Year,Month21077, 21089,2,2018,8)
tablename TEXT; -- table name
indexname TEXT; -- index name
indexname1 TEXT; -- index name
sequencename TEXT;
createtablescr TEXT;
indexscr TEXT; -- index on booking date column
indexscr1 TEXT; -- index on booking date column
inserttablescr TEXT;
sequencescr TEXT;
selecttablescr TEXT;
bwtid integer;
begin
tablename = schemaName || '.' || TableNameCol;
indexname = TableNameCol || '_SourceBranchId_idx';
indexname1 = TableNameCol || '_DestinationBranchId_idx';
sequencename = TableNameCol || '_BranchWiseTurnOverId_seq';
sequencescr = 'CREATE SEQUENCE IF NOT EXISTS ' || sequencename || ' `enter code here`INCREMENT 1 START 1 CACHE 1';
createtablescr = 'CREATE TABLE IF NOT EXISTS ' || tablename || ' (
BranchWiseTurnOverId SERIAL PRIMARY KEY,
AccountGroupId NUMERIC(10,0) not null,
SourceBranchId NUMERIC(10,0) not null,
DestinationBranchId NUMERIC(10,0) not null,
WayBillTypeId NUMERIC(1,0) not null,
TotalLR NUMERIC(20,0) not null,
TotalBookingAmount NUMERIC(20,2) not null,
TotalDeliveryAmount NUMERIC(20,2) not null,
GrandTotalAmount NUMERIC(20,2) not null,
TotalQuantity NUMERIC(20,0) not null,
TotalActualWeight NUMERIC(20,3) not null,
TotalChargedWeight NUMERIC(20,3) not null,
BookingMonth NUMERIC(2,0) not null,
BookingYear NUMERIC(4,0) not null
);';
indexscr = 'CREATE INDEX IF NOT EXISTS ' || indexname || ' ON ' || tablename || ' (SourceBranchId);';
indexscr1 = 'CREATE INDEX IF NOT EXISTS ' || indexname1 || ' ON ' || tablename || ' (DestinationBranchId);';
selecttablescr = 'select branchwiseturnoverid INTO bwtid from '|| tablename || ' where AccountGroupId='|| AccountGroupIdCol ||' and SourceBranchId='|| SourceBranchIdCol ||' and DestinationBranchId='|| DestinationBranchIdCol ||' and WayBillTypeId='||WayBillTypeIdCol ||';';
inserttablescr = 'INSERT INTO ' || tablename || '(AccountGroupId, SourceBranchId, DestinationBranchId, WayBillTypeId, TotalLR, TotalBookingAmount,
TotalDeliveryAmount, GrandTotalAmount, TotalQuantity, TotalActualWeight, TotalChargedWeight, BookingMonth, BookingYear)
VALUES (' || AccountGroupIdCol || ',' || SourceBranchIdCol || ',' || DestinationBranchIdCol || ',' || WayBillTypeIdCol || ',' || TotalLRCol
|| ',' || TotalBookingAmountCol || ',' || TotalDeliveryAmountCol || ',' || GrandTotalAmountCol || ',' || TotalQuantityCol || ',' || TotalActualWeightCol
|| ',' || TotalChargedWeightCol || ',' || BookingMonthCol || ',' || BookingYearCol || ');';
EXECUTE sequencescr;
EXECUTE createtablescr;
EXECUTE indexscr;
EXECUTE indexscr1;
EXECUTE inserttablescr;
EXECUTE selecttablescr;
RAISE NOTICE USING MESSAGE = bwtid;
end;
$$
language 'plpgsql';
select dashboard.rspCreateAndInsertUpdateBranchWiseTurnOverTable(270,19168,19168,4,1,1000,1000,1000,5,50,50,6,2019,'dashboard','branchwiseturnover_2019_6');
解决方案
要回答直接问题“如何打印 btwid”,请使用以下语法:
RAISE NOTICE '%', bwtid;
推荐阅读
- r - 编写良好数据集以执行混合模型分析的棘手方法
- python - 如何在 Mac 上离线运行 VPython
- pandas - 迭代 Pandas DataFrame 的重复分区/组
- json - 我如何阅读这个 json 并迅速将其更改为字典?
- c++ - 需要一些关于 C++ 中“交换”函数的帮助
- javascript - Mat-Error 首次打开表单时显示必需的验证错误消息
- c++ - 使用 for 循环的 C++ 中的分段错误
- c# - WPF Datagrid 列标题中的文本框过滤器
- html - 如何在 Visual Studio Code 中设置默认浏览器?
- docker - 支持最多架构的 Docker 基础镜像?