sql - 在 Postgres 中使用表值参数调用函数

Question

我从早上开始就遇到了一个问题，花了很多时间，但未能调用给定的函数。

函数定义:

CREATE OR REPLACE FUNCTION public.proc_mc2cdnpf_insertupdatev3(
    tblnotesv3 typupdate_notesv3,
    tbldoclinks typupdate_guidparameter,
    iuserid integer,
    shtmltext character varying,
    OUT snoteid character varying,
    OUT inoteid integer,
    OUT inoteactivityid integer)
    RETURNS record
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
AS $BODY$    
#variable_conflict use_variable
    declare         sNote VARCHAR;
    declare sLoggedInUser VARCHAR(20);
    declare     dtCurrDateTime timestamp;
    declare     iCurrDate int;
    declare     iCurrTime INT;
    declare iNewNoteID INT;
BEGIN

/*
    proc_MC2CDNPF_InsertUpdateV3
    2018-04-23      Dennis Sebenick

    2018-04-23      
        - Initial creation of new proc for storing additional HTML text value.  
        - This proc is going to help bridge the old note system to a new note storage method

    2018-04-25
    - Added iNoteID / iNoteActivityID for output

    2018-06-01
    - Update to typUpdate_NotesV3 - removed additional CDN rows for long text

    2091-05-08
        Rupali Shah
        web 1753-added RTRIM(isnull(NOTE_HDQTRS,''))  while creating sNoteID

*/
/******************************
** File:  proc_MC2CDNPF_InsertUpdateV3
** Desc:  Insert/Update account notes
** Auth:  Rupali Shah
** Date:  2019-05-08
**************************
** Change History
**************************
** Date                                Dev                        JIRA                                       Description 
**2019-05-08                Rupali Shah                 Web 1753                        -added RTRIM(isnull(NOTE_HDQTRS,'')) while creating sNoteID
*******************************/
    SELECT dtCurrDateTime = fnGetDate();
    SELECT iCurrDate = fnMC2DateToMC2(dtCurrDateTime);
    SELECT iCurrTime = fnMC2DateTimeToMC2(dtCurrDateTime);

    /*==========================================
        Retrieve Update fields from parameter
    ============================================*/
    select  
          NoteID,
          NOTE_NOTES,
          NOTE_LOGGEDINUSER,
          coalesce(NOTE_ID, 0)
        INTO SNoteID,sNote,sLoggedInUser,iNewNoteID
    from 
        tblNotesV3
    LIMIT 1;

    /************************************************
        2018-04-18      DKS
        Added in to update new note table
    *************************************************/
    IF (iNewNoteID > 0)
    THEN
    BEGIN
        IF (LENGTH(sNote) > 0)
        THEN
        BEGIN
            UPDATE 
                NoteDetails
            SET
                sNote = sNote,
                sNoteHTML = sHTMLText,
                iNoteEditedBy = iUserID,
                dtNoteEdited = dtCurrDateTime
            WHERE
                iNoteID = iNewNoteID;

            IF (ROWCOUNT = 0)
            THEN
            BEGIN
                INSERT INTO 
                    NoteDetails
                    (
                        iNoteType,
                        sNote,
                        sNoteHTML,
                        iNoteEnteredBy,
                        dtNoteEntered
                    )
                    VALUES
                    (   9,         -- iNoteType - int
                        sNote,       -- sNote - VARCHAR(max)
                        sHTMLText,       -- sNoteHTML - VARCHAR(max)
                        iUserID,         -- iNoteEnteredBy - int
                        dtCurrDateTime
                ) RETURNING iNewNoteID;

            END;
            END IF;
        END;
        END IF;
    END;
    ELSE
    BEGIN
        -- Inserting a New Activity
        -- 2018-04-18       DKS
        -- - Insert to new note table.
        IF (LENGTH(sNote) > 0)
        THEN
        BEGIN
            INSERT INTO 
                NoteDetails
                (
                    iNoteType,
                    sNote,
                    sNoteHTML,
                    iNoteEnteredBy,
                    dtNoteEntered
                )
                VALUES
                (   9,         -- iNoteType - int
                    sNote,       -- sNote - VARCHAR(max)
                    sHTMLText,       -- sNoteHTML - VARCHAR(max)
                    iUserID,         -- iNoteEnteredBy - int
                    dtCurrDateTime
            ) RETURNING iNewNoteID;
        END;
        END IF;
    END;
    END IF;

    /************************************************
        End new note table insert / update
    *************************************************/

    IF EXISTS(SELECT * FROM MC2CDNPF WHERE MC2CDNPF.iNoteID = iNoteID)
    THEN
    BEGIN
        UPDATE 
            MC2CDNPF
        SET
            CDNNOTES = '',
            CDNFDATE = fnMC2DateToMC2(TblNotesUpdate.NOTE_DDATE),
            CDNREASN = TblNotesUpdate.NOTE_REASN,
            CDNPRIOR = TblNotesUpdate.NOTE_PRIOR,
            CDNTAGGED = TblNotesUpdate.NOTE_TAGGED,
            iNoteID =  iNewNoteID
        FROM
            MC2CDNPF
        JOIN
            tblNotesV3 TblNotesUpdate
        ON
            MC2CDNPF.iNoteID = TblNotesUpdate.NOTE_ID
        WHERE
            MC2CDNPF.CDNDSEQN = 1;

    END;
    ELSE
    BEGIN
        SELECT 

                TRIM(coalesce(NOTE_CMPANY,'')) || 
                TRIM(coalesce(NOTE_BUSNSS,'')) || 
                TRIM(coalesce(NOTE_CUSNBR,'')) ||
                TRIM(coalesce(NOTE_ENTITY,'')) ||
                TRIM(coalesce(NOTE_HDQTRS,'')) ||
                CAST( iCurrDate AS VARCHAR(8)) || 
                CAST( iCurrTime AS VARCHAR(10))
                INTO sNoteID
        FROM
            tblNotesV3
        LIMIT 1;

        INSERT INTO
            MC2CDNPF
            (
                CDNDSEQN, 
                CDNNOTES, 
                CDNCMPANY, 
                CDNCUSNBR, 
                CDNBUSNSS, 
                CDNENTITY, 
                CDNHDQTRS, 
                CDNRPTCON,
                CDNFULNME, 
                CDNAGNIDN, 
                CDNDDATE, 
                CDNDTIME, 
                CDNREASN,
                CDNTUSER, 
                CDNADATE, 
                CDNTAGGED, 
                CDNFDATE, 
                CDNPRIOR,  
                CDNDTASRC, 
                CDNODATE, 
                CDNOTIME,
                iNoteID
            )
        SELECT
            1, 
            '',                             -- 6/1/2018 DKS - no longer storing note in MC2CDNPF
            coalesce(NOTE_CMPANY,''), 
            coalesce(NOTE_CUSNBR,''), 
            coalesce(NOTE_BUSNSS,''), 
            coalesce(NOTE_ENTITY,''), 
            coalesce(NOTE_HDQTRS,''), 
            coalesce(NOTE_RPTCON,''),
            NOTE_FULNME, 
            coalesce(NOTE_AGNIDN,''), 
            iCurrDate, 
            iCurrTime, 
            NOTE_REASN, 
            NOTE_TUSER, 
            iCurrDate, 
            coalesce(NOTE_TAGGED,''), 
            iCurrDate, 
            NOTE_PRIOR, 
            coalesce(NOTE_DTASRC,''),
            iCurrDate, 
            iCurrTime,
            iNewNoteID
        FROM
            tblNotesV3 TblSource;

    END;
    END IF;

    -- There are attachments to link
    IF EXISTS(SELECT * FROM tblDocLinks)
    THEN
    BEGIN
        CALL public.proc_Documents_AddLinkMultiple_LinkID (tblDocLinks, 9, 0, sNoteID, iUserID);
    END;
    END IF;
     iNoteID := iNewNoteID;
     iNoteActivityID := 0;  

END;
$BODY$;

我试图通过以下两种方式调用我的函数:

方法一:

SELECT public.proc_MC2CDNPF_InsertUpdateV3
(
    (SELECT w::typupdate_notesv3 FROM (TABLE tblnotesv31) w ) ,
    (SELECT w1::typupdate_guidparameter FROM (TABLE tbldoclinks1) w1 ) ,
    1,
    'test text'
)  

但它失败并出现以下错误:

ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function proc_mc2cdnpf_insertupdatev3(typupdate_notesv3,typupdate_guidparameter,integer,character varying) line 41 at SQL statement SQL state: 42601

方法二:

SELECT * 
from proc_MC2CDNPF_InsertUpdateV3
(
    (SELECT w::typupdate_notesv3 FROM (TABLE tblnotesv31) w ) ,
    (SELECT w1::typupdate_guidparameter FROM (TABLE tbldoclinks1) w1 ) ,
    1,
    'test text'
)  

它再次失败说:

ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function proc_mc2cdnpf_insertupdatev3(typupdate_notesv3,typupdate_guidparameter,integer,character varying) line 41 at SQL statement SQL state: 42601

有人可以帮我看看我的函数调用到底出了什么问题吗？

Answer 1

从第 41 行开始(正如错误消息告诉您的那样)您得到了:

SELECT dtCurrDateTime = fnGetDate();
SELECT iCurrDate = fnMC2DateToMC2(dtCurrDateTime);
SELECT iCurrTime = fnMC2DateTimeToMC2(dtCurrDateTime);

我假设您想在那里设置变量。但是你做错了。 (看起来你试图使用 SQL Server 语法。这是将函数从 SQL Server 移植到 Postgres 的尝试吗？还有 BEGIN ... END block 用于 IFs 和 ELSEs，它们在 Postgres 中是不必要的(但无害)但在 SQL Server 中是必需的。)

要么使用INTO:

SELECT fnGetDate() INTO dtCurrDateTime;
SELECT fnMC2DateToMC2(dtCurrDateTime) INTO iCurrDate;
SELECT fnMC2DateTimeToMC2(dtCurrDateTime) INTO iCurrTime;

或者，由于您实际上并不是在查询表，简单的赋值也应该有效:

dtCurrDateTime = fnGetDate();
iCurrDate = fnMC2DateToMC2(dtCurrDateTime);
iCurrTime = fnMC2DateTimeToMC2(dtCurrDateTime);

可能还有其他行有同样的错误，你应该检查整个代码。