What do I use so far for PL/pgSQL development – Aginity and psql.  psql is the only tool that Amazon official supports for development of pl/pgsql – at least that is what support told me and my experience confirms – FREE GUI tools like Aginity and SQLWorkBenchJ have bugs… fortunately there are some work arounds.

Well for most of my everyday stuff I use Aginity with Redshift on Windows.  It is a free GUI and that works pretty well with Redshift but… support for pl/pgsql as I mentioned is limited.

First off, just to get PL/pgSQL to compile… In an Aginity Query Window / Tab you have to do a “Execute as single batch”.  And procedures don’t show up in the object browser.

Also when you drop a procedure you have to specify dummy parameters to match the procedure’s parameter list.  But, since you can’t view it, what if you don’t have the source code to know what those parameters were?

Solution: Look up the procedure signature with:

select regprocedureout(c.oid::regprocedure) from pg_proc c where proname='test_sp1';
regprocedureout
-------------------------------------
test_sp1(integer,character varying)

Then you can drop with (where the schema admin owns the procedure):

drop admin.test_sp1(snum integer, schar character varying);

Hope this helps… Mike

Leave a Reply

Your email address will not be published. Required fields are marked *