CODEWITHSUNDEEP
sqlamazon-web-servicesstored-proceduresamazon-redshift
curiosityrock
curiosityrock

Reputation: 213

How to use a stored procedure inside of a select statement in Redshift

I have found some other workarounds for mysql and other database providers but i wasn't able to find a solution to use stored procedure (get_data) inside of a select statement in redshift. I have a stored procedure passing some ids and creating a temp table (tmp_tbl) from another table. Then i want to use that table and return some data from the temp table with some manipulations.

SELECT
    tl.code,
    tl.name,
    SUM(tl.imps) as sales,
    SUM(tl.clicks) as clicks
FROM (

    CALL get_data('id1,id2');

    SELECT * FROM tmp_tbl tl

)
WHERE filter='1990'
GROUP BY 1,2;

As a solution, i could possibly do the stored procedures separately somewhere else, and then combine the results but that means i'll have to make two separate requests from my database and i want to avoid it. Or i came up with other solutions where i still make a one database call without using a stored procedure inside a select statement but i have some other codes relying on this structure so i would like to achieve an inside statement. Is there a way to do this?

The procedure;

CREATE OR REPLACE PROCEDURE get_data(f1 varchar(max))
AS $$
BEGIN
  DROP TABLE IF EXISTS id_list;

  CREATE TEMPORARY TABLE id_list (
            Id varchar(40)
    );

  DROP TABLE IF EXISTS tmp_tbl;
  FOR loop_var IN 1..(REGEXP_COUNT(f1,',') + 1) LOOP
            INSERT INTO id_list VALUES (SPLIT_PART(f1,',',loop_var));
  END LOOP;
  CREATE TEMP TABLE tmp_tbl as 

    SELECT code, name,sales,clicks
    FROM mview_a
    WHERE id IN (SELECT id FROM id_list)

UNION ALL

    SELECT code, name,sales,clicks
    FROM mview_b
    WHERE id IN (SELECT id FROM id_list)


UNION ALL

    SELECT code, name,sales,clicks
    FROM mview_c
    WHERE id IN (SELECT id FROM id_list)

UNION ALL

    SELECT code, name,sales,clicks
    FROM view_d (combination of multiple mviews)
    WHERE id IN (SELECT id FROM id_list)

UNION ALL

    SELECT code, name,sales,clicks
    FROM mview_e
    WHERE id IN (SELECT id FROM id_list)

Upvotes: 1

Views: 3498

Answers (1)

dfundako
dfundako

Reputation: 8324

Can't you just run the CALL first to make the temp table, then use it in the subsequent join?

CALL get_data('id1,id2');

SELECT
    tl.code,
    tl.name,
    SUM(tl.imps) as sales,
    SUM(tl.clicks) as clicks
FROM tmp_tbl tl
WHERE filter='1990'
GROUP BY 1,2;

Upvotes: 1

Related Questions