1

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$;
aan anna philip
  • 61
  • 1
  • 9
  • If you want to return something, use a (set returning) function. Procedures aren't meant to return result sets. –  Mar 31 '20 at 05: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 Mar 31 '20 at 05:42
  • https://dbfiddle.uk/?rdbms=postgres_12&fiddle=b4dd53f81a866c0642a26d7b0dc2e81a –  Mar 31 '20 at 05:45
  • Alright. function which I had added above is working without any issues. my question is it possible to convert as postgreSQL procedure ? – aan anna philip Mar 31 '20 at 05:51
  • @LaurenzAlbe if you look at this https://www.postgresql.org/docs/11/sql-createprocedure.html , it says "The mode of an argument: IN, INOUT, or VARIADIC. If omitted, the default is IN. ( **OUT arguments are currently not supported for procedures. Use INOUT instead.** )" . Could we use INOUT instead OUT ? – aan anna philip Mar 31 '20 at 06:01
  • 1
    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 Mar 31 '20 at 06:10
  • 1
    And why the insistence on a function or procedure for a simple statement that would better be encapsulated in a VIEW –  Mar 31 '20 at 06:12

1 Answers1

3

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

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

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263