PL/pgSQL syntax error at or near "table"

Question

I've been learning some PL/pgSQL and am having a hard time declaring and using a table type.

I know this script is dumb and doesn't accomplish anything, I'm just trying to learn.

Here is my query:

do $$
DECLARE
   TYPE employee_collection IS TABLE OF employee%ROWTYPE;
   emp_data employee_collection;
BEGIN
   SELECT * 
   BULK COLLECT INTO emp_data
   FROM employee;
END;
$$

When I run this I get this error: syntax error at or near "TABLE".

Everywhere I look online this looks like the way to do this. I even copied most of this code from a website.

Is there a syntax error or is something else going on?

I'm running PostgreSQL 12.11.

Answer 1

PostgreSQL can use PL/pgSQL, PL/Tcl, PL/Perl and PL/Python.

PL/SQL is the scripting language used by Oracle Database.
Despite their naming, the two are completely incompatible.