How to run a true procedure in pgAdmin and evaluate results

Question

I haven't seen any question that covers all of what I'm asking, so for grins am posting my journey here.

The task: Run, in pgAdmin, a procedure (not a function) in Postgres. Evaluate and report back the results to the user on-screen.

For my situation, I had a procedure with the following signature:

CREATE OR REPLACE PROCEDURE sqls.transform_main(
    INOUT returnval integer)
LANGUAGE 'plpgsql'
...

How does one accomplish this?

What do you mean by on-screen? If I just run `CALL test(1)`, I see the result in Data Output window. — Jeremy, Oct 02 '19 at 13:00
Is `test()` a function? Or a procedure? `sqls.transform_main` is a procedure and I see no output of the value in variable `x` in the data output window or anywhere else when I comment out the `pg_notify()` line in the code I provided in my answer below. — Wellspring, Oct 02 '19 at 13:10
However with pg_notify() added, I see a popup which informs me of the value in `x` and the same info also shows up in the Notifications area. — Wellspring, Oct 02 '19 at 13:16
Yes, it was a procedure with one INOUT argument, like yours. What version of pgadmin are you using? — Jeremy, Oct 02 '19 at 13:17
4.12 -- am I that far out of date? Can you post your code and pgAdmin version, so that I can understand what you're doing differently? Thx! — Wellspring, Oct 02 '19 at 13:20
Wait, @Jeremy this is not making sense. How can you pass an integer directly into an INOUT parameter?? And then report back meaningfully the returned value? That code won't even run, let alone return answers back. Am I missing something? If I pass in 1, I get this: procedure parameter "returnval" is an output parameter but corresponding argument is not writable. — Wellspring, Oct 02 '19 at 13:34

score 1 · Accepted Answer · answered Oct 02 '19 at 15:20

Replying (with code) to Jeremy's reply...

OK, I am seeing how your solution works. One question, though...

I want to pass in an actual variable that is defined in such a way that I can evaluate the returned value. (Assume this this an extended script that I don't want to turn into another stored procedure or function...)

Can I do that by your approach? I am thinking no, because by the time I've made it a script (not a single SQL call) I lose the output that I do see in your version, as you noted, in the Data Output tab.

DO
$$
declare return_val integer;
declare return_msg text;
declare notify_msg text := 0;
BEGIN
call test(return_val); -- I never see what this value is unless...
if (return_val != 0) then
    notify_msg = 'Failure (' || return_val || ')';
else
    notify_msg = 'success!';
end if;
RAISE NOTICE 'Return value is %', notify_msg; -- ...unless I do something like this
END
$$

As my familiarity with how Postgres works grows, I see that my solution (seems to me) is the one that works *if* you want to declare variables and use them after your procedure call. If that's not necessary, @Jeremy provides the shortest route and it does indeed post back answers into the Data Output window, because his script is a straight SQL call, so to speak, whereas mine is not. — Wellspring, Oct 05 '19 at 16:12
It's spring and the nice weather coming back after a long winter...LOL — Tarik, Apr 18 '21 at 18:05

Wellspring · Answer 2 · 2019-10-02T13:13:58.463

0

The answer that worked for me:

(Edit: listen virtual; added to code below)

DO
$$
declare x integer;
declare y text;
BEGIN
listen virtual;
call sqls.transform_main(x);
y = 'Return value: ' || x;
perform pg_notify('virtual', y);
END
$$

If anyone else can improve on this, I'm all ears. Just thankful for something, at this point, having burned more time than I'd like to admit on the task.

edited Oct 02 '19 at 13:13

answered Oct 02 '19 at 12:37

Wellspring

1,128
1
10
19

Another option that is less obtrusive than the popups: `RAISE NOTICE 'Return Value %', y;` – Wellspring Oct 02 '19 at 14:19

score 0 · Answer 3 · answered Oct 02 '19 at 14:33

I'm putting this down here because it's not helpful to paste code in the comments. Here's an example in psql. It's similar in pgadmin, except the results appear in the Data Output window.

# CREATE OR REPLACE PROCEDURE test(
    INOUT returnval integer)
LANGUAGE 'plpgsql' AS
$$
BEGIN
  returnval := returnval;
END;
$$
;
CREATE PROCEDURE

# call test(1);
 returnval
-----------
         1
(1 row)

How to run a true procedure in pgAdmin and evaluate results

3 Answers3