- 최신
- 최다 투표
- 가장 많은 댓글
CREATE OR REPLACE VIEW public.v_get_stored_proc_params AS WITH arguments AS (SELECT oid, arg_num , arg_names[arg_num] AS arg_name , arg_types[arg_num - 1] AS arg_type FROM (SELECT GENERATE_SERIES(1, arg_count) AS arg_num , arg_names, arg_types, oid FROM (SELECT oid , proargnames AS arg_names , proargtypes AS arg_types , pronargs AS arg_count FROM pg_proc WHERE proowner != 1 AND prolang = 100356 ) t) t) SELECT n.nspname AS schema_name , p.proname AS proc_name , p.oid::INT AS proc_id , a.arg_num AS order , NVL(a.arg_name, '') AS parameter , FORMAT_TYPE(a.arg_type, NULL) AS data_type FROM pg_proc p LEFT JOIN pg_namespace n ON n.oid = p.pronamespace LEFT JOIN arguments a ON a.oid = p.oid WHERE p.proowner != 1 AND p.prolang = 100356 ORDER BY 1,2,3,4;
https://docs.aws.amazon.com/redshift/latest/dg/r_PG_PROC_INFO.html
PG_PROC_INFO
PG_PROC_INFO is an Amazon Redshift system view built on the PostgreSQL catalog table PG_PROC and the internal catalog table PG_PROC_EXTENDED. PG_PROC_INFO includes details about stored procedures and functions, including information related to output arguments, if any.
Here is a sample query over this view:
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 ;
Once you know the name of the stored procedure you can retrieve the DDL to create it using SHOW PROCEDURE my_proc;
Create a view so you can keep using it:
CREATE OR REPLACE VIEW
public.v_get_stored_proc_params AS
SELECT
n.nspname,
b.usename,
p.proname,
p.prosrc
FROM
pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p ON
pronamespace = n.oid
JOIN pg_catalog.pg_user b on
b.usesysid = p.proowner
WHERE
nspname not in ('information_schema',
'pg_catalog')
the answer looks correct but may do with some formatting