redshift procedure with out parameter and dynamic table name as in parameter

Question

I'm trying to make a redshift procedure that will give output on any table like say a row count for example. But I can't figure out how you are supposed to pass in a table name dynamically and get output. I'm trying to do something like this:

CREATE OR REPLACE PROCEDURE my_schea.test(t in varchar, o out varchar) 
LANGUAGE plpgsql 
AS $$ 
BEGIN  execute 'SELECT count(*) into ' || o || ' FROM ' || t || ';';
               -- or something more complicated
 END  
$$ ;

then I call like this:

call my_schema.test('myschema.mytable');

I can't seem to get the syntax right, any help would be appreciated. this specific syntax above gives me an error of:

 cannot EXECUTE a null query string;

Stefanov.sm · Answer 1 · 2021-03-18T20:07:02.543

There are a few things to change.

out o varchar is invalid; out parameters are not allowed in procedures, only inout ;
into o does not work in dynamic sql because it does not 'see' the local variables. See execute.

So the procedure sample becomes -

CREATE OR REPLACE PROCEDURE my_schema.test(in t text, inout o bigint default null)  as
$$
begin
   execute ('SELECT count(*) FROM '||t) into o; -- NB *** SQLi prone ***
   -- or something more complicated
end;
$$ language plpgsql;

Then you can

call my_schema.test('myschema.mytable');

and it will yield a result similar to that of a select statement. I assume that you have a good reason to write a procedure and not a function since the example looks very much like one. As a function the sample would look like this:

create or replace function my_schema.testf(t text) returns bigint  as
$$
declare 
   retval bigint;
begin
   execute ('SELECT count(*) FROM '||t) into retval; -- NB *** SQLi prone ***
   -- or something more complicated
   return retval;
end;
$$ language plpgsql;

and then

select my_schema.testf('myschema.mytable');

Hi thank you for answering this question. I've given this a try and two things. First I don't think you can write Redshift functions in plpgsql see: https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_FUNCTION.html. Second, I'm not able to get the stored procedure create query to run either. I get an error with the 'default' yet even when i remove the ```default null``` I still get a ```syntax error at or near ```. So something is not quoted right — J.Hammond, Mar 22 '21 at 18:54
Well, these are working procedure and function but in Postgresql. I am afraid that I can not help much with Redshift dialect.Yet try `o out bigint` or integer, w/o default. I see that the syntax differs a bit. — Stefanov.sm, Mar 22 '21 at 20:47

redshift procedure with out parameter and dynamic table name as in parameter

1 Answers1