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.

• mustaccio: If you're using Postgres, PL/SQL (an Oracle thing) is not something you want to (or can) use. Read the Postgres documentation for the correct Postgres syntax.
• Vérace: I even copied most of this code from a website. - what website - you say "most" - please show us all of this code and the bits you took - if it was an Oracle PL/SQL site, it's hardly surprising that it won't work exactly as is, even though PL/pgSQL is more or less an effort at cloning Oracle's procedural language.

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.

• James: Thanks for the answer. I don't understand why it was downvoted. I added LANGUAGE plpgsql to the end and the error is the same.
• Phill W.: Yes, you will get the same error because the code you posted is ORACLE database PL/SQL and NOT PostgreSQL plpgsql. Different DBMS, different scripting languages. The /only/ way that code will work is if you run it in an ORACLE database. There is no way to make ORACLE PL/SQL work inside PostgreSQL. Deliberately misquoted movie time: "You must write in pgplsql. Don't write in PL/SQL and transpose."