Calling function with table valued parameters in Postgres

Question

I have been facing a problem from since morning and have spent many hours but failed to call below given function.

Function definition:

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$;

I am trying to call my function in following two ways:

Method1:

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

But it fails with following error:

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

Method2:

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

Again it failed saying:

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

Can please someone help me out that what's actually wrong with my function call?

Post the complete function.

sticky bit
– sticky bit

2019-11-15 11:51:52 +00:00
Commented Nov 15, 2019 at 11:51 — sticky bit
– sticky bit, Commented Nov 15, 2019 at 11:51
@stickybit added

Muhammad Waheed
– Muhammad Waheed

2019-11-15 11:56:46 +00:00
Commented Nov 15, 2019 at 11:56 — Muhammad Waheed
– Muhammad Waheed, Commented Nov 15, 2019 at 11:56

sticky bit · Accepted Answer · 2019-11-15 12:06:50Z

2

Starting at line 41 (as the error message told you) you got:

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

I assume you want to set the variables there. But you're doing it wrong. (It looks like you tried to use SQL Server syntax. Is this an attempt to port a function from SQL Server to Postgres? There are also BEGIN ... END blocks for IFs and ELSEs, which are unnecessary (but harmless) in Postgres but needed in SQL Server.)

Either use INTO:

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

Or, since you're not actually querying a table, simple assignments should work too:

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

There might be other lines with the same mistake, you should check the whole code.

answered Nov 15, 2019 at 12:06

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Muhammad Waheed Over a year ago

ERROR: query has no destination for result data CONTEXT: PL/pgSQL function proc_mc2cdnpf_insertupdatev3(typupdate_notesv3,typupdate_guidparameter,integer,character varying) line 111 at SQL statement SQL state: 42601

sticky bit Over a year ago

@MuhammadWaheed: Basically the same thing. A RETURNING ... is following the INSERT ... but isn't addressed to go anywhere. Remove the RETURNING ... if you don't need that value or assign it to a variable with INTO ....

Muhammad Waheed Over a year ago

ERROR: syntax error at end of input LINE 107: ) RETURNING into iNewNoteID; ^ SQL state: 42601 Character: 3470

Muhammad Waheed Over a year ago

Above error appears when I change it to RETURNING INTO

sticky bit Over a year ago

The syntax is ... RETURNING <column_name> INTO <variable_name>. But you seem to try ... RETURNING INTO <column_name>.

|

Collectives™ on Stack Overflow

Calling function with table valued parameters in Postgres

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related