CODEWITHSUNDEEP
amazon-redshiftplpgsqldynamic-sql
Henrov
Henrov

Reputation: 1610

Write stored procedure with dynamic SQL

I want to write a stored procedure that takes a textparameter (table_name) and that executes a query based on that tablename: select count(*) from and that writes the value found to a table.

It is the (SELECT COUNT(*) FROM VARTABLE_NAME) part that I do not know how to implement in Amazon Redshift

CREATE OR REPLACE  PROCEDURE SP_LOGGING (VARSTART INT, VARRUNID INT , VARPHASE VARCHAR, VARTABLE_NAME VARCHAR)
AS $$

BEGIN
            IF VARSTART = 1 THEN
                BEGIN
                UPDATE AUDIT.RUN_TEST
                SET TABLE_NAME = VARTABLE_NAME
                ,"no_of_records_at_start" = (SELECT COUNT(*) FROM VARTABLE_NAME)
                END;

            ELSEIF VARSTART = 0 THEN    
                BEGIN
                UPDATE AUDIT.RUN_TEST
                SET TABLE_NAME = VARTABLE_NAME
                ,"no_of_records_at_end" = (SELECT COUNT(*) FROM VARTABLE_NAME)
                END;
            END IF;
END;
$$ LANGUAGE PLPGSQL;

Upvotes: 0

Views: 183

Answers (1)

Joe Harris
Joe Harris

Reputation: 14035

You need to use EXECUTE to run dynamic SQL.

CREATE PROCEDURE sp_logging (
      varstart      INT
    , varrunid      INT 
    , varphase      VARCHAR
    , vartable_name VARCHAR ) 
AS $$
BEGIN
    IF varstart = 1 THEN
        EXECUTE 'UPDATE audit.run_test
                    SET table_name = '||vartable_name||'
                      , start_rows = (SELECT COUNT(*) FROM '||vartable_name||' )';
    ELSEIF varstart = 0 THEN    
        EXECUTE 'UPDATE audit.run_test
                    SET table_name = '||vartable_name||'
                      , end_rows = (SELECT COUNT(*) FROM '||vartable_name||')';
    ELSE 
        RAISE ERROR 'Value of `varstart` must be 1 or 0. Received `%`.',varstart;
    END IF;
END;
$$ LANGUAGE PLPGSQL;

Upvotes: 2

Related Questions