Redshift Spectrum Procedure cannot be called inside a select SQL

Question

I am currently migrating a PostgreSQL procedure to work in Redshift Spectrum Serverless. I was able to have a working procedure that works as intended in Redshift. However, its originally used inside a SQL select statement and I am currently not able to do the same from Redshift. Any idea how can I achieve this?

Original PostgreSQL Procedure:

CREATE OR REPLACE FUNCTION sp_test()
    RETURNS date AS
$$
SELECT test_date FROM test_table
$$ LANGUAGE sql;

Orginal Usage:

insert into random_table
select sp_test()

New Procedure for Redshift

create or replace procedure sp_test(out v_test_date date)
as $$
BEGIN
    select test_date into v_test_date from test_table;
    end;
$$ language plpgsql;

Attempted New SQL which isn't working:

insert into random_table
select sp_test()

ERROR: sp_test() is a procedure
Hint: To call a procedure, use CALL.

PS: I know using CALL sp_test() works but I want to use it with an insert which is not working. So any help wrt how to make this work would be really appreciated.

Answer 1

Thank you @mp24 for the suggestion. I got my code working as follows:

create or replace procedure sp_test(inout v_test_date date) --have to make this INOUT to work with embedded SP
as $$
BEGIN
    select test_date into v_test_date from public_internal.test_table;
end;
$$ language plpgsql;

create or replace procedure sp_insert_in_random()
as $$
    declare
        v_test_date date;
BEGIN
    v_test_date = '20230101'; -- providing a dummy date to work with INOUT parameter
    call sp_test(v_test_date);
    insert into public_internal.random_table select v_test_date;
end;
$$ language plpgsql;

call sp_insert_in_random();

Post this I could see the data inserted in my table as expected.