agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Jason Aleski <jason.aleski@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Stored Procedure to return resultset from multiple delete statements.
Date: Tue, 4 Aug 2015 22:54:29 -0500
Message-ID: <55C188F5.7020904@gmail.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

I have a function that will purge an item out of our inventory system.  
This script works and displays the appropriate information in the 
messages/notices pane.  I would like it to return the notices in a 
resultset format because only developers have access to the 
messages/notices pane.  I would like to display the results as a 
resultset in my application.  The problem is that I'm issuing multiple 
delete commands which cannot be joined.  I tried creating a temp table 
at the top of the procedure, then inserted data (rows affected) into the 
table; but I could not get that method to work. Can anyone point me in a 
direction on what to look at to get the "table affected" and the "number 
of rows affected by the delete" into some sort of result set?  Below is 
my current procedure that is working.  There are actually 3 more tables 
that need to be purged, but I removed those for now.

CREATE OR REPLACE FUNCTION purgeInventoryItemByCode (IN t text)

    RETURNS void AS

$BODY$

DECLARE

    RCprice_history             int;

    RCinventory_transaction_log int;

    RCitem_code                 int;

BEGIN

    --Purging price history of item.

    DELETE FROM price_history

          WHERE item_id IN (SELECT row_id

                                FROM item

                               WHERE item_code = $1);

    IF found

    THEN

       GET DIAGNOSTICS RCprice_history = ROW_COUNT;

       RAISE NOTICE 'DELETE % row(s) FROM price_history', RCprice_history;

    END IF;

    --Purging item from inventory transaction log.

    DELETE FROM inventory_transaction_log

           WHERE item_id IN (SELECT row_id

                                FROM item

                               WHERE item_code = $1);

    IF found

    THEN

       GET DIAGNOSTICS RCinventory_transaction_log = ROW_COUNT;

       RAISE NOTICE 'DELETE % row(s) FROM inventory_transaction_log', RCinventory_transaction_log;

    END IF;

    --Purging item from Master Items table

    DELETE FROM items

          WHERE item_code = $1;

    IF found

    THEN

       GET DIAGNOSTICS RCitem_code = ROW_COUNT;

       RAISE NOTICE 'DELETE % row(s) FROM items', RCitem_code;

    END IF;

END;

$BODY$

    LANGUAGE plpgsql

    VOLATILE

    COST 100

-- 
Jason Aleski / IT Specialist



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

view thread (2+ messages)  latest in thread

Message-ID: <55C188F5.7020904@gmail.com>
Permalink:  ../55C188F5.7020904@gmail.com/
Also on:    postgresql.org/message-id/55C188F5.7020904@gmail.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jason.aleski@gmail.com
  Subject: Re: Stored Procedure to return resultset from multiple delete statements.
  In-Reply-To: <55C188F5.7020904@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox