agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedStored Procedure to return resultset from multiple delete statements.
2+ messages / 2 participants
[nested] [flat]
* Stored Procedure to return resultset from multiple delete statements.
@ 2015-08-05 03:54 Jason Aleski <jason.aleski@gmail.com>
2015-08-05 05:21 ` Re: Stored Procedure to return resultset from multiple delete statements. David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Jason Aleski @ 2015-08-05 03:54 UTC (permalink / raw)
To: pgsql-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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Stored Procedure to return resultset from multiple delete statements.
2015-08-05 03:54 Stored Procedure to return resultset from multiple delete statements. Jason Aleski <jason.aleski@gmail.com>
@ 2015-08-05 05:21 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: David G. Johnston @ 2015-08-05 05:21 UTC (permalink / raw)
To: Jason Aleski <jason.aleski@gmail.com>; +Cc: pgsql-sql
On Tuesday, August 4, 2015, Jason Aleski <jason.aleski@gmail.com> wrote:
> 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.
>
>
>
Your solution will most easily be accomplished using a combination of
"delete ... returning", insert, and cte/with.
David J.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2015-08-05 05:21 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-08-05 03:54 Stored Procedure to return resultset from multiple delete statements. Jason Aleski <jason.aleski@gmail.com>
2015-08-05 05:21 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox