CODEWITHSUNDEEP
stored-proceduresamazon-redshiftplpgsql
J.Hammond
J.Hammond

Reputation: 270

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

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;

Upvotes: 1

Views: 4166

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13049

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');

Upvotes: 1

Related Questions