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.

Answer 2

You would need to embed the call to your stored procedure in another stored procedure and call the insert statement there. Unfortunately, Redshift is really restricted when it comes to stored procedures and (even more) stored functions.