CODEWITHSUNDEEP
stored-proceduresamazon-redshiftplpgsql
Golokesh Patra
Golokesh Patra

Reputation: 596

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

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.

Upvotes: 1

Views: 9306

Answers (2)

Apoorva Bhardwaj
Apoorva Bhardwaj

Reputation: 1

You can also use the below-given syntax:

SELECT INTO Count count(*) from table_name;

By doing this you are inserting the value of count(*) into variable Count. It works.

Upvotes: 0

Joe Harris
Joe Harris

Reputation: 14035

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

Upvotes: 1

Related Questions