Redshift Stored Procedure - [Amazon](500310) Invalid operation: syntax error at or near "$1";

Question

My Agenda is to store the counts of the 2 tables ( being passed in the parameter ) and then do some more operations upon comparing the both.

PROBLEM -

Stored Procedure throwing Error :

CREATE OR REPLACE PROCEDURE dev.gp_count_matching_20191204(actual_tablename character varying(256), bkp_tablename character varying(256))
 LANGUAGE plpgsql
AS $$
DECLARE
actual_table_name varchar(256);
backup_table_name varchar(256);
actual_count_query varchar(1024);
actual_count int;
backup_count_query varchar(1024);
backup_count int;
BEGIN
call dev.gp_test_error_handling_tablename_format(actual_tablename);
call dev.gp_test_error_handling_tablename_format(bkp_tablename);

actual_count:=(select count(*) as counts from actual_tablename);
--raise info 'Actual Table Name - %, Actual Table Count - %',actual_tablename,actual_count;
end;
$$

This throws the following Error while creating the stored procedure-

An error occurred when executing the SQL command:
CREATE OR REPLACE PROCEDURE dev.gp_count_matching_20191204(actual_tablename character varying(256), bkp_tablename character varying(256))
 LANGUAGE pl...

[Amazon](500310) Invalid operation: syntax error at or near "$1";
1 statement failed.

Execution time: 0.99s

If I comment out the actual_count:=(select count(*) as counts from actual_tablename); then the Stored Procedure gets created Successfully.

I guess it has something to do with me using the parameter ( since $1 points the first parameter ) in the query. Since I am pretty new with Stored procedure, I unable to figure out the exact problem.

Thanks in Advance.

Thanks @a_horse_with_no_name, Previous -> actual_count:=(select count() into actual_count from actual_tablename); post_change -> select count() into actual_count from actual_tablename; Though the procedure gets created but the same error appears when i call the procedure. — Golokesh Patra
You can't pass a table name as a parameter. You need dynamic SQL — a_horse_with_no_name
hmm, interesting , Last time i checked redshift doesnt allow Dynamic Sql Queries. @a_horse_with_no_name, then storing it in a variable like - actual_table_name:=actual_tablename; and then substituting this value this variable in that query doesnt help either. So, my question is , Will changing the language from plpgsql help me here ? If it will, then will there be any issue(compatibility) , if i use this stored procedure written in some other language in another stored procedure written in plpgsql . — Golokesh Patra

Joe Harris Joe Harris · Accepted Answer · 2019-12-09T21:44:45

You need to use EXECUTE when running dynamic SQL. In your example the query is in parentheses but nothing is making it execute. To execute the query into a variable you using the INTO syntax

sql := 'SELECT …'
EXECUTE sql_var INTO result_var;

Please see the example Stored Procedures in our GitHub repo "Amazon Redshift Utils". https://github.com/awslabs/amazon-redshift-utils/tree/master/src/StoredProcedures

There are several examples that use dynamic SQL, such as https://github.com/awslabs/amazon-redshift-utils/blob/master/src/StoredProcedures/sp_split_table_by_range.sql

Redshift Stored Procedure - [Amazon](500310) Invalid operation: syntax error at or near "$1";

1 Answers