重庆思庄Oracle、Redhat认证学习论坛

标题: PG如何获取视图的定义SQL语句 [打印本页]

作者: 郑全 时间: 2023-8-2 14:37
标题: PG如何获取视图的定义SQL语句
比如我想获取: v_test 视图的定义，可以使用以下SQL来完成：

select schemaname,viewname,viewowner,definition
from pg_catalog.pg_views
where viewname='v_test';

下面是一个真实例子：

# select schemaname,viewname,viewowner,definition from pg_catalog.pg_views where viewname='mdc2_mz_cost_view';
schemaname |    viewname    | viewowner |                                                                                                                                  definition

------------+-------------------+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
public    | mdc2_mz_cost_view | postgres  |  SELECT DISTINCT '0'::text AS hiscode,
                                                                                 +
         |                |          |    pb."PatId" AS patientid,
                                                                                 +
         |                |          |    ((pb."IdentityNo" || '_'::text) || pb."PatOriginId") AS clinicno,
                                                                                 +
         |                |          |    b."BalanceNo" AS receiptno,
                                                                                 +
         |                |          |    pb."BillId" AS expense_sn,
                                                                                 +
         |                |          |    pb."BillNo" AS prescno,
                                                                                 +
         |                |          |       CASE a."ChargesCategoryCode"
                                                                                 +
         |                |          |          WHEN '5'::text THEN '1'::text
                                                                                 +
         |                |          |          WHEN '6'::text THEN '1'::text
                                                                                 +
         |                |          |          WHEN '7'::text THEN '1'::text
                                                                                 +
         |                |          |          WHEN 'F'::text THEN '2'::text
                                                                                 +
         |                |          |          WHEN 'G'::text THEN '2'::text
                                                                                 +
         |                |          |          WHEN 'C'::text THEN '3'::text
                                                                                 +
         |                |          |          WHEN 'D'::text THEN '4'::text
                                                                                 +
         |                |          |          ELSE '5'::text
                                                                                 +
         |                |          |       END AS costtype,
                                                                                 +
         |                |          |    a."ChargesId" AS itemcode,
                                                                                 +
         |                |          |    a."ChargesName" AS itemname,
                                                                                 +
         |                |          |    (t."药品剂型")::text AS drugform,
                                                                                 +
         |                |          |    a."Spec" AS drugspec,
                                                                                 +
         |                |          |    (cc."产地")::text AS drugsccj,
                                                                                 +
         |                |          |    a."Unit" AS itemunit,
                                                                                 +
         |                |          |    a."Quantity" AS itemnum,
                                                                                 +
         |                |          |    a."Received" AS cost,
                                                                                 +
         |                |          |    a."Price" AS item_unit_price,
                                                                                 +
         |                |          |    to_char(pb."OccurTime", 'yyyy-mm-dd hh24:mi:ss'::text) AS costtime,
                                                                                 +
         |                |          |    pb."BillingDeptId" AS deptcode,
                                                                                 +
         |                |          |    pb."BillingDept" AS deptname,
                                                                                 +
         |                |          |    ( SELECT (t5."编号")::text AS "编号"
                                                                                 +
         |                |          |          FROM staff_dict t5
                                                                                 +
         |                |          |          WHERE (t5."ID" = to_number(pb."BillingPersonId", 'fm999999'::text))) AS doctorcode,
                                                                                 +
         |                |          |    pb."BillingPerson" AS doctorname,
                                                                                 +
         |                |          |    ''::text AS medgroupcode,
                                                                                 +
         |                |          |    ''::text AS medgroupname,
                                                                                 +
         |                |          |       CASE
                                                                                 +
         |                |          |          WHEN ((o."诊疗类别")::text = ANY (ARRAY[('5'::character varying)::text, ('6'::character varying)::text, ('7'::character varying)::text])) THEN
                                                                                 +
         |                |          |          CASE
                                                                                 +
         |                |          |                WHEN (POSITION(('中药'::text) IN ((o2."医嘱内容")::text)) > 0) THEN '水煎煮'::text
                                                                                 +
         |                |          |                ELSE (o2."医嘱内容")::text
                                                                                 +
         |                |          |          END
                                                                                 +
         |                |          |          ELSE ''::text
                                                                                 +
         |                |          |       END AS routecode,
                                                                                 +
         |                |          |    ((a."OrderId" || '_'::text) || o."序号") AS cid,
                                                                                 +
         |                |          |       CASE
                                                                                 +
         |                |          |          WHEN (b."InsuranceBalanceStatusCode" IS NULL) THEN '2'::text
                                                                                 +
         |                |          |          ELSE '1'::text
                                                                                 +
         |                |          |       END AS ipayclass
                                                                                 +
         |                |          | FROM "ProcessedBill" pb,
                                                                                 +
         |                |          |    "BalanceBill" bb,
                                                                                 +
         |                |          |    "Balanced" b,
                                                                                 +
         |                |          |    LATERAL json_to_recordset(pb."BillContent") a("BillDetailsId" text, "OrderId" text, "ChargesCategoryCode" text, "ChargesId" text, "ChargesName" text, "SerialNumber" text, "Spec" text, "Quantity" text, "Unit" text, "Price" text, "Received" text),                      +
         |                |          |    (((orders o
                                                                                 +
         |                |          |    LEFT JOIN drug_characteristics t ON ((t."药名ID" = o."诊疗项目ID")))
                                                                                 +
         |                |          |    LEFT JOIN drug_dict cc ON (((o."收费细目ID")::text = (cc."药品ID")::text)))
                                                                                 +
         |                |          |    LEFT JOIN orders o2 ON ((o."相关ID" = o2."ID")))
                                                                                 +
         |                |          | WHERE ((pb."BillId" = bb."BillId") AND (bb."BalanceId" = b."BalanceId") AND (pb."PatOriginCode" = '01'::text) AND (a."OrderId" IS NOT NULL) AND (b."BalanceStatusCode" = '1'::text) AND (a."OrderId" = (o."ID")::text) AND (pb."OccurTime" > (now() + '-60 days'::interval)));
(1 row)

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)