aws redshift - how to retrieve out parameters of stored procedure

0

Please give a query to retrieve out parameters for a particular stored procedure by using a sql query in aws redshift . thank you.

asked 2 years ago2094 views
2 Answers
0

Please look at the answers to this question.

An example of query from one of the answer is:

SELECT p.proname sp_name , n.nspname sp_schema , p.proargnames sp_args 
FROM pg_catalog.pg_namespace n 
JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid 
WHERE p.proowner > 1 AND p.prolang = 100125 ;
AWS
EXPERT
answered 2 years ago
0

Here is an example from our documentation:

CREATE OR REPLACE PROCEDURE inner_proc(INOUT a int, b int, INOUT c int) LANGUAGE plpgsql
AS $$
BEGIN
  a := b * a;
  c := b * c;
END;
$$;

CREATE OR REPLACE PROCEDURE outer_proc(multiplier int) LANGUAGE plpgsql
AS $$
DECLARE
  x int := 3;
  y int := 4;
BEGIN
  DROP TABLE IF EXISTS test_tbl;
  CREATE TEMP TABLE test_tbl(a int, b varchar(256));
  CALL inner_proc(x, multiplier, y);
  insert into test_tbl values (x, y::varchar);
END;
$$;

CALL outer_proc(5);
      
SELECT * from test_tbl;
 a  | b
----+----
 15 | 20
(1 row)  

The inner_proc has an INOUT parameter and you can see that the outer_proc called it with "y". This variable started out as 4 but was modified with inner_proc to now be 20. https://docs.aws.amazon.com/redshift/latest/dg/r_CALL_procedure.html

profile pictureAWS
EXPERT
answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions