Convert PostgreSQL function to PostgreSQL Stored Procedure

Question

I'm migrated a schema from oracle to postgreSQL using AWS-SCT, packages are converted to functions in postgreSQL. I need to convert this function to procedure to compact with the code in the application middleware.

I've tried to convert to procedure, everytime i'm getting error like SQL state: 42601 and inout parameter are permitted

Kindly help to convert the function to procedure.

CREATE OR REPLACE Function "pk_audfreq$sp_audfreq"(
    OUT out_a double precision,
    OUT out_b double precision,
    OUT out_c double precision)
    RETURNS record
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$
DECLARE
BEGIN    
    SELECT
        MIN(audresponsetime), AVG(audresponsetime), MAX(audresponsetime)
        INTO STRICT out_a, out_b, out_c
        FROM public.audio_freq;        
    END;
$BODY$;

If you want to return something, use a (set returning) function. Procedures aren't meant to return result sets. — user330315
– user330315, Commented Mar 31, 2020 at 5:37
tried, still giving syntax error SQL state: 42601, Help required to correct the function to procedures in PostgreSQL without syntax error — aan anna philip
– aan anna philip, Commented Mar 31, 2020 at 5:42
Sure you can use INOUT, but then you have to supply arguments. Why the insistence on using a procedure for something that naturally is a function? — Laurenz Albe
– Laurenz Albe, Commented Mar 31, 2020 at 6:10
And why the insistence on a function or procedure for a simple statement that would better be encapsulated in a VIEW — user330315
– user330315, Commented Mar 31, 2020 at 6:12

Laurenz Albe · Accepted Answer · 2020-03-31 06:08:51Z

3

You cannot have OUT parameters with procedures (yet). As the documentation states:

OUT arguments are currently not supported for procedures. Use INOUT instead.

answered Mar 31, 2020 at 6:08

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

aan anna philip Over a year ago

Cool, I've created a procedure with INOUT, giving an error message inout arguments are permitted

Laurenz Albe Over a year ago

Unless you show the relevant code and the complete error message, it is hard to help.

Collectives™ on Stack Overflow

Convert PostgreSQL function to PostgreSQL Stored Procedure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related