CODEWITHSUNDEEP
oracle-databaseamazon-redshiftplpgsql
Emanuel Oliveira
Emanuel Oliveira

Reputation: 116

How to execute Redshift procedural PL/pgSQL code like a script?

I've been wondering if it's possible to write a-la oracle “anonymous block" script. Basically run any Redshift procedure code but removing the create procedure 1st line, in client like SQL Workbench/J? Is it possible and/or does we need to tweak some config in SQL Workbench/J?

Declare
Xxx integer;

BEGIN
 Null;
 any PL/pgSQL code;
 
EXCEPTION when others
 Raise xxx ;
 
END;
$$ LANGUAGE plpgsql;

Or - ultimately - would/can this be possible using postgres command line https://www.postgresql.org/docs/current/app-psql.html

Upvotes: 1

Views: 3439

Answers (2)

mherzog
mherzog

Reputation: 1230

You can create a procedure with a name which indicates it is temporary. You can then run the procedure and then delete it.

While not optimal, it works in the sense that it runs procedural code for debugging or one-time purposes.

This will conflict with other users who create a procedure with the same name. In the example below, I used a procedure name with my initials.

create or replace procedure mh_test()
as
$$
declare
  x float;
begin
<<Execute your code here>>
end;
$$
language plpgsql
;

call mh_test()
;

drop procedure mh_test();
;

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658312

would/can this be possible using postgres command line

PostgreSQL has the DO command for just that purpose since version 9.0. Your example would translate to something like:

DO
$do$
DECLARE
   xxx integer;
BEGIN
   -- any PL/pgSQL code;
EXCEPTION WHEN OTHERS THEN  -- optional
   RAISE EXCEPTION 'My text: xxx';
END
$do$;

But Redshift does not support that. After being forked at version 8.0.2, it differs in many aspects.

Upvotes: 1

Related Questions